CODEWITHSUNDEEP
excelpowershell
Jamee L
Jamee L

Reputation: 19

PowerShell Remove Newline Characters From Excel Cells

I have a script that allows me to iterate over every cell (even the unused ones), but when I set the text property of the cell to remove new lines with nothing, I get an error that I can't update all cells even though the script is only updating one cell at a time:

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$workbook = $Excel.Workbooks.Open("D:\Excel\excel.xlxs")
$worksheet = $Workbook.Sheets.Item(1)

$allcells = $worksheet.UsedRange.Cells
$total = $allcells.columns.count
$everycell = $worksheet.Range("1:$total")

foreach ($cell in $everycell)
{
    if ($cell.Text -ne "")
    {
        $cell.Text = $cell.Text.Replace("`r`n","")
    }
}

$workbook.Save()
$excel.Quit()

To iterate over used cells within a range and remove characters from the cells (in this case a new line), is there a different way to replace all characters in all cells text equal to something else? The manual way of doing this would be to load Excel, and use control and h key to just replace. I would think there's a way to do this as well with the com object.

Upvotes: 0

Views: 1254

Answers (1)

Doug Maurer
Doug Maurer

Reputation: 8878

In my testing, $worksheet.Range("1:$total") was 49152 cells. Here is what I used to simplify removing newlines from each used cell.

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$workbook = $Excel.Workbooks.Open("D:\Excel\excel.xlsx")
$worksheet = $Workbook.Sheets.Item(1)
$allcells = $worksheet.UsedRange.Cells
foreach($cell in $allcells)
{
    $cell.value = $cell.value2 -replace '\r?\n'
}
$workbook.Save()
$excel.Quit()

Couple of things I wanted to point out. First, you have a typo in the excel filename, maybe that is just in this posting. Second, text is a read only field, you need to set value as shown.

Upvotes: 1

Related Questions