text to columns trouble with "zeros"
 

Hi

I want to get 01-0000000-00000001 to look like below...


Cell a1 = 01
Cell b1 = 0000000
Cell c1 = 00000001

I keep getting

Cell a1 = 1
Cell b1 = 0
Cell c1 = 1

I use "-" as my delimiter....


Thanks,
ExcelUser777

text to columns trouble with "zeros"
 

You have to specify that the output columns are Text, not General, in Step 3
of the Wizard.
Or if you record a macro of this action, you get something like:

Range("K1").Select
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar
_
:="-", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2)), _
TrailingMinusNumbers:=True

NickHK

"ExcelUser777" wrote in message
s.com...
Hi

I want to get 01-0000000-00000001 to look like below...


Cell a1 = 01
Cell b1 = 0000000
Cell c1 = 00000001

I keep getting

Cell a1 = 1
Cell b1 = 0
Cell c1 = 1

I use "-" as my delimiter....


Thanks,
ExcelUser777

text to columns trouble with "zeros"
 

NickHK,

I tried setting the wizard in step 3 to "text" and that didn't work


Thanks,
lax_777


NickHK wrote:
You have to specify that the output columns are Text, not General, in Step 3
of the Wizard.
Or if you record a macro of this action, you get something like:

Range("K1").Select
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar
_
:="-", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2)), _
TrailingMinusNumbers:=True

NickHK

"ExcelUser777" wrote in message
s.com...
Hi

I want to get 01-0000000-00000001 to look like below...


Cell a1 = 01
Cell b1 = 0000000
Cell c1 = 00000001

I keep getting

Cell a1 = 1
Cell b1 = 0
Cell c1 = 1

I use "-" as my delimiter....


Thanks,
ExcelUser777

text to columns trouble with "zeros"
 

For each of the fields?

I'd try it again.

ExcelUser777 wrote:

NickHK,

I tried setting the wizard in step 3 to "text" and that didn't work

Thanks,
lax_777

NickHK wrote:
You have to specify that the output columns are Text, not General, in Step 3
of the Wizard.
Or if you record a macro of this action, you get something like:

Range("K1").Select
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar
_
:="-", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2)), _
TrailingMinusNumbers:=True

NickHK

"ExcelUser777" wrote in message
s.com...
Hi

I want to get 01-0000000-00000001 to look like below...


Cell a1 = 01
Cell b1 = 0000000
Cell c1 = 00000001

I keep getting

Cell a1 = 1
Cell b1 = 0
Cell c1 = 1

I use "-" as my delimiter....


Thanks,
ExcelUser777


--

Dave Peterson

text to columns trouble with "zeros"
 

Guys ... I should of realized that the first part of the string
changed...when I choose text...

It works !!!

thanks to Dave and NickHK



Dave Peterson wrote:
For each of the fields?

I'd try it again.

ExcelUser777 wrote:

NickHK,

I tried setting the wizard in step 3 to "text" and that didn't work

Thanks,
lax_777

NickHK wrote:
You have to specify that the output columns are Text, not General, in Step 3
of the Wizard.
Or if you record a macro of this action, you get something like:

Range("K1").Select
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar
_
:="-", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2)), _
TrailingMinusNumbers:=True

NickHK

"ExcelUser777" wrote in message
s.com...
Hi

I want to get 01-0000000-00000001 to look like below...


Cell a1 = 01
Cell b1 = 0000000
Cell c1 = 00000001

I keep getting

Cell a1 = 1
Cell b1 = 0
Cell c1 = 1

I use "-" as my delimiter....


Thanks,
ExcelUser777


--

Dave Peterson