How to convert all columns to Text, while using TextToColumns, by default it was in General format - Using Powershell

Question

I need to split texts using delimiter |.But after split its truncating front zeros:

Input

0001|00030455|testing
0003|00004532|testing

Expected Output

0001  00030455  testing
0003  00004532  testing

Getting Output like this

1  30455  testing
3  4532   testing

Tried this

$colA=$wst.range("A1").EntireColumn
$colrange=$wst.range("A1")
$colA.texttocolumns($colrange,1,-4142,$false,$false,$false,$false,$false,$true,"|",@(1,3))

Option in Excel
How to use above option in TextToColumn() using powershell?

user_478656 · Answer

From the Range.TextToColumns documentation it looks like your @(1,3) is where the column types should go, and that's saying the first column should be a date in MDY date format, which doesn't look right.

Instead, I think that should be:

@((1,2),(2,2),(3,2))

# so

$colA.TextToColumns($colRange,1,-4142,$false,$false,$false,$false,$false, $true,"|",@((1,2),(2,2),(3,2)))

Which is column 1 type 2 (xlTextFormat), column 2 type 2, column 3 type 2.

That seems to work for me in a quick test:

How to convert all columns to Text, while using TextToColumns, by default it was in General format - Using Powershell

1 Answers