Submitted By: Kent Finkle

Parses comma-separated values in a spreadsheet column into multiple columns (for example, the value Olympia, WA becomes two separate values: Olympia and WA).

PowerShell
Edit|Remove
$comments = @'
Script name: Convert-TextToColumns.ps1
Created on: Sunday, July 08, 2007
Author: Kent Finkle
Purpose: How can I use Windows Powershell to
Use the Text to Columns Feature in Microsoft Excel?
'@
#-----------------------------------------------------
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers() 
}
#-----------------------------------------------------
$xlDelimited = 1
$xlTextQualifierNone = -4142 

$xl = new-object -comobject excel.application
$xl.Visible = $True
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)
 
$ws.Cells.Item(1, 1).Value() = "Olympia, WA"
$ws.Cells.Item(2, 1).Value() = "Salem, OR"
$ws.Cells.Item(3, 1).Value() = "Boise, ID"
$ws.Cells.Item(4, 1).Value() = "Sacramento, CA"
 
$r = $ws.Range("A1").EntireColumn

$r2 = $ws.Range("B1")
 
$a = $r.TextToColumns( `
    $r2,$xlDelimited,$xlTextQualifierNone,$False,$False,$False,$True)
 
$a = Release-Ref($r2)
$a = Release-Ref($r)
$a = Release-Ref($ws)
$a = Release-Ref($wb)
$a = Release-Ref($xl)