# Searching Excel spreadsheet for text

**URL:** <https://forums.powershell.org/t/searching-excel-spreadsheet-for-text/13623>\
**Category:** PowerShell Help\
**Created:** [January 7, 2020, 10:54am UTC](https://forums.powershell.org/t/searching-excel-spreadsheet-for-text/13623 "2020-01-07T10:54:24Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![firmbyte](https://avatars.discourse-cdn.com/v4/letter/f/e68b1a/32.png) [@firmbyte](https://forums.powershell.org/u/firmbyte)\
**Post date:** [January 7, 2020, 10:54am UTC](https://forums.powershell.org/t/searching-excel-spreadsheet-for-text/13623/1 "2020-01-07T10:54:24Z")

</div>

I’m trying to search an Excel column for specific text, I pass the workbook name, sheet number and column letter and the text I’m searching for but it is not found. I know the text I’m searching for is in the column, why is it not being found, what am i doing wrong?

```
$SearchText = 'I/O operation has been aborted'

$File = 'C:\Iain\Empyrean\DBAReport\DBAReport\_20200106\_070005.xlsx'

$Excel = New-Object -ComObject Excel.Application

$Workbook = $Excel.Workbooks.Open($File)

$workSheet = $Workbook.Sheets.Item(5)

$Range = $Worksheet.Range("F1").EntireColumn

$Range.Find($SearchText)
```

Example of entry that I'm searching for:

| Time | ComputerName | LevelDisplayName | EventID | ProviderName | Message |
| 1/4/2020 18:16 | P01 | Error | 18210 | MSSQL$PROD03 | BackupIoRequest::ReportIoError: write failure on backup device '//P03/SQLBackups/PROD03/csprod/P03\_PROD03\_csprod\_Full\_202001041721.safe'. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.). |

&nbsp;

---

<div class="post-metadata">

**Author:** ![grokkit](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/grokkit/32/970_2.png) [@grokkit](https://forums.powershell.org/u/grokkit)\
**Post date:** [January 7, 2020, 2:33pm UTC](https://forums.powershell.org/t/searching-excel-spreadsheet-for-text/13623/2 "2020-01-07T14:33:43Z")

</div>

It looks like your spaces are actually the middle dot (interpunct) character (either [U+2E31](https://codepoints.net/U+2E31?lang=en) or [U+00B7](https://www.compart.com/en/unicode/U+00B7)). Are you using Notepad++ with the show invisible characters option turned on?

When I copied your code, searching for the string

```
I/O operation has been aborted
```

does not work, however if I retype the string

```
I/O operation has been aborted
```

locally, it does work. I think the Find() method for Excel is searching for the literal interpunct character and not finding it.

_Edit_ Also, just FYI, you have your $workSheet variable as **workSheet** on line 6 but **Worksheet** on line 7. This isn’t affecting your functionality, it’s just inconsistent.

---

<div class="post-metadata">

**Author:** ![firmbyte](https://avatars.discourse-cdn.com/v4/letter/f/e68b1a/32.png) [@firmbyte](https://forums.powershell.org/u/firmbyte)\
**Post date:** [January 7, 2020, 7:36pm UTC](https://forums.powershell.org/t/searching-excel-spreadsheet-for-text/13623/3 "2020-01-07T19:36:11Z")

</div>

The dots are added when I copy & paste from VS Code. But I re-wrote the entire script in Powershell\_ise and still get no results.

```
$SearchText = 'I/O operation has been aborted'
$File = 'C:\Iain\Empyrean\DBAReport\DBAReport_20200106_070005.xlsx'
$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open($File)
$workSheet = $Workbook.Sheets.Item(5)
$Range = $Worksheet.Range("F1").EntireColumn
$Range.Find($SearchText)
```

The problem seems to be that my script is only reading row 1 of F column. I want to read all the rows searching for the text…?

&nbsp;

&nbsp;

---

<div class="post-metadata">

**Author:** ![firmbyte](https://avatars.discourse-cdn.com/v4/letter/f/e68b1a/32.png) [@firmbyte](https://forums.powershell.org/u/firmbyte)\
**Post date:** [January 7, 2020, 7:53pm UTC](https://forums.powershell.org/t/searching-excel-spreadsheet-for-text/13623/4 "2020-01-07T19:53:31Z")

</div>

I’ve got it working, by adding this

```
$Rows = ($worksheet.UsedRange.Rows).count
for ($i=1; $i -le $Rows -1; $i++) {
$row = "f$i"
$Range = $Worksheet.Range($row).EntireColumn
$Range.Find($SearchText)
}
```

---

<div class="post-metadata">

**Author:** ![grokkit](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/grokkit/32/970_2.png) [@grokkit](https://forums.powershell.org/u/grokkit)\
**Post date:** [January 13, 2020, 6:38pm UTC](https://forums.powershell.org/t/searching-excel-spreadsheet-for-text/13623/5 "2020-01-13T18:38:16Z")

</div>

After doing some further reading, it seems that [Find](https://docs.microsoft.com/en-us/office/vba/api/excel.range.find#remarks) returns the first match that it discovers in the specified range and then stops, so using EntireColumn just tells Find to check the whole column for **a** match, **not** to find _every_ match in the column. However, you can re-use the parameters of a Find search with the [FindNext](https://docs.microsoft.com/en-us/office/vba/api/excel.range.findnext) method (which is basically analogous to the way the Find dialogue works in the Excel application).

Here is an [example script](https://mcpmag.com/articles/2018/04/06/find-excel-data-with-powershell.aspx) in which the author implements FindNext in a Do loop after an initial Find search in order to find all matches in the desired search range.

---

<div class="post-metadata">

**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\
**Post date:** [May 16, 2024, 8:30pm UTC](https://forums.powershell.org/t/searching-excel-spreadsheet-for-text/13623/6 "2024-05-16T20:30:50Z")

</div>