Replace string in Excel with PowerShell

Replace strings in cells in Excel using PowerShell.
Imagine operating and executing Excel’s search and replace function from PowerShell.

Environment

• Windows 10
• PowerShell 5.1

Replace strings

Here is the PowerShell script.
Replace the file path as appropriate for your environment.

try{
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $excel.DisplayAlerts = $false

    # Open xlsx file
    $wb = $excel.Workbooks.Open("C:\path\to\Excel\file.xlsx")

    # Specify sheet
    $sheet = $wb.ActiveSheet

    # Replacement processing (searches for and replaces cells that exactly matches the search string)
    [void]$sheet.Cells.Replace("Hello", "Hi", [Microsoft.Office.Interop.Excel.XlLookAt]::xlWhole)
    # Replacement processing (searches for and replaces cells that partially match the search string)
    [void]$sheet.Range("A1", "C4").Replace("World", "Kenya", [Microsoft.Office.Interop.Excel.XlLookAt]::xlPart)

    # Save changes
    $wb.Save()

    $excel.Quit()

}finally{
    $sheet, $wb, $excel | ForEach-Object {
        if ($_ -ne $null) {
            [void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($_)
        }
    }
}

The processing flow is briefly described as follows.

1. Open xlsx file
   Open the specified Excel file(Open)。

2. Specify sheet
   Store the active sheet in a variable.
   This is because we will process this sheet later.

3. Perform string substitution
   Once you get the object that represents the cell range ($sheet.Cells、$sheet.Range("A1", "C4")), specify the search string and the replacement string and perform the replacement(Replace)。
   This time, the XlLookAt option is specified. An option that specifies whether the search string is searched for an exact or partial match for the cell. Please refer to the following screenshot for the operation.
   By the way, there are other options. Click here for details。

4. Save
   Save the file.(Save)。

Below are screenshots of the excel file before and after replacement.

Look at the image above. In this script, “Hello” is replaced with “Hi”, but since the option to search by exact match (XlLookAt :: xlWhole) is specified, “Hello” in A4 cell is Not replaced.
In contrast, “World” has been replaced with “Kenya” in both B2 and A4 cells.

References

• Range.Replace method (Excel)
• XlLookAt enumeration (Excel)

Related Posts