2

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?

Dinesh Kumar
  • 72
  • 6

1 Answers1

2

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:

Excel spreadsheet showing the output with leading zeros

TessellatingHeckler
  • 27,511
  • 4
  • 48
  • 87
  • @DineshKumar You're welcome :) (Please upvote or click the 'accept answer' tick if it's helpful) – TessellatingHeckler Feb 22 '21 at 23:20
  • Is that any possible ways to give that in a single shot, because i have more than 300 columns. Did i need to do like this @((1,2)(2,2),........(300,2)) or any other easy ways. – Dinesh Kumar Feb 22 '21 at 23:37
  • @DineshKumar I don't know if it will work, but you could try to generate that first as `[array]$formats = 1..300 | foreach-object { ,@($_, 2) }` and then use `$formats` in the call to `TextToColumns`. – TessellatingHeckler Feb 23 '21 at 00:17