Replace string in Excel with PowerShell

Published:  

powershell excel


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

Excel search and replace function

Excel search and replace function

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.

Before replacement

Before replacement

After replacement

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



Related Posts