Search code examples
excelpowershellcom

Trim a excel cell with PowerShell

Is it possible to trim a excel cell like this:

<newline>
<newline>
A
B
C
<newline>
<newline>

to:

A
B
C

I tried it with the COM Object:

$excel = New-Object -ComObject Excel.Application
$wb = $excel.Workbooks.Open($path)
foreach ($ws in $wb.Worksheets){
[void]$ws.Cells.Trim()
}

But Trim() is not a valid method. Is there any other way to Trim excel cell(s) for a complete worksheet or a given range ($ws.Range) ?

Solution

  • Trim() is a method on System.String. You must use it on a cell's value, not the cell itself, and then update the changed value. I used UsedRange but you can change it to any other range:

    $excel = New-Object -ComObject Excel.Application
$wb = $excel.Workbooks.Open($path)
foreach ($ws in $wb.Worksheets){
    foreach ($cell in $ws.UsedRange) {
        if ($cell.Value2 -ne $null) {
            $cell.Value2 = $cell.Value2.Trim()
        }
    }
}

    Don't forget to close or cleanup the Excel object after.