# Replace string in Excel with PowerShell

Published:

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.

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.

## Related Posts

### How to open Excel file with password in PowerShell

Open an Excel workbook with password in PowerShell. When …

### Convert Excel file to PDF with PowerShell

Introducing a PowerShell script that converts an Excel file …

### [Python] Replace text in Excel cells

Replace strings in Excel cells with Python. It can be used …

### How to convert Excel file to PDF with Python

Describes how to convert an Excel sheet to PDF using Python. …

2020 (4)
2019 (6)