My TextToColumns VBA is not keeping leading zeros...please help

[bsumesh]

Hi all,
I'm a VBA newbie, I wrote the vba below and it works, but I loose the leading zeros as well as zeros after decimals whenever the string contains any numbers. I need to keep the zeros because they are ICD9 codes. I have hundreds of tables like this, so it is not feasible to use the inbuilt Text To Column function. I use excel 2010. Can anyone help??

Sub Text2Columns()
Selection.TextToColumns _
Destination:=Range("B1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=True, _
OtherChar:="-"
End Sub



Thnaks a lot,
SK

 

[starl]

You need to include the FieldInfo Argument so that you can tell Excel to treat the data as Text, which is the only way to keep leading zeros. FieldInfo is an array that includes (as the 2nd dim) the formatting of the column)

Recommendation: Record a macro doing the Text To Columns as needed, including the formatting of the columns.

 

[bsumesh]

Thank you for your response. I followed your suggestion. I recorded a macro, please see the modified VBA code below. Unfortunately my problem still exists. The values in the resulting columns still looses the leading zeros.


Sub Text2Column()


Cells.Select
Selection.NumberFormat = "@"
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, OtherChar _
:="-", FieldInfo:=Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1)), TrailingMinusNumbers:=True

End Sub

 

[starl]

The only column I see formatted is Text is the first column. Is that the column you are referring to that is not retaining the zeros? Because I did dummy some data (23.45,00023.2) and it did work fine.
You have to set the format for EACH column...

 

[bsumesh]

Thank you starl for the tip, I got it worked. I modified the code like this:

[COLOR=#333333]Sub Text2Column()[/COLOR]
[COLOR=#333333]Cells.Select[/COLOR]
[COLOR=#333333]Selection.NumberFormat = "@"[/COLOR]
[COLOR=#333333]Columns("A:A").Select[/COLOR]
[COLOR=#333333]Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _[/COLOR]
[COLOR=#333333]TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _[/COLOR]
[COLOR=#333333]Semicolon:=False, Comma:=True, Space:=False, Other:=False, OtherChar _[/COLOR]
[COLOR=#333333]:="-", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, _[/COLOR]
[COLOR=#333333]2)), TrailingMinusNumbers:=True[/COLOR]
[COLOR=#333333]End Sub

[/COLOR][COLOR=#333333]

[/COLOR]