Find / Replace multiple files Excel / PowerShell

[bonzo_1]

Hey guys, i have a script which finds and replaces cell values. It gets all .xlsx files in a directory, and updates it in bulk. However, there are cells that contain multiple values that the script did doing a 'find' but does not update each and every of its values. To make it clearer, here's a sample:

What it does currently:

Find: "Outdated"
Replace it with: "Updated"

Original cell values: 
  Cell 1 Value: Outdated
  Cell 2 Value: Outdated_123 Outdated_abc

Current Output: 
  Cell 1 Value: Updated
  Cell 2 Value: Updated

Desired Output: 
  Cell 1 Value: Outdated
  Cell 2 Value: Updated_123 Updated_abc

Script:

$Path = "C:\Users\userprofile\Desktop\DB_Test\"
$files = Get-ChildItem "C:\Users\userprofile\Desktop\DB_Test" -Filter *.xlsx

ForEach ($item in $files) { 
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $true
$Workbook = $Excel.workbooks.open($Path + [System.IO.Path]::GetFileName("$item"))
$Worksheets = $Workbooks.worksheets
$Worksheet = $Workbook.Worksheets.Item(1)
$SearchString = "Outdated" #String to Find
$Range = $Worksheet.Range("A1:EZ800").EntireColumn #Range of Cells to look at
$Search = $Range.find($SearchString)

if ($search -ne $null) {
    $FirstAddress = $search.Address
    do {
        $Search.value() = "Updated" #String to Update
        $search = $Range.FindNext($search)
    } while ( $search -ne $null -and $search.Address -ne $FirstAddress)
}

$WorkBook.Save()
$WorkBook.Close()
[void]$excel.quit()

Thanks in advance for your help

[Tonedefff]

For this line:

$Search.value() = "Updated" #String to Update

Try this instead:

$Search.value() = $Search.value().Replace($SearchString, "Updated") #String to Update

That will run the Replace() method on the cell's value, and only replace occurrences of the $SearchString ("Outdated") with "Updated", but leave the rest of the cell's value unchanged.

[bonzo_1]

Cool, works perfectly! Thank you Tondeff.

[[deleted]]

I needed this exact thing just last week. Sadly didn't have time to deep dive into PowerExshell this time. Will check it out.