TEXT TO COLUMNS WITH LEADING ZEROS

[Guest]

When I try to use Text To Columns to split a long series of numbers into
several columns of numbers, if one of the numbers starts with a zero it isn't
retained. The zero gets dropped. I tried formatting the original numbers as
text - it didn't work.
Is there a way to split a numerical string into several columns and retain
leading zeroe?

 

[Norman Jones]

Hi Peggy,

The final screen of the TextToColumns wizard gi ves you the option of
selecting the format for each column. Select the text format option and all
should be well.

 

[kesse]

I may be missing something but I don't understand how this fixes the problem.
I'm trying to seperate 9 digits out into 9 columns. All of them have leading
zeros that need to be retained. But when I go to TextToColumns it doesn't
even bring in the leading zeros into the data preview. Selecting Text on step
3 of 3 doesn't change anything. There's no delimiter so I'm trying to do it
by fixed width unless you have another suggestion for that.

Thanks

 

[Gord Dibben]

Assuming you have a text string in A1 of 010203040

Data>Text to Columns>Fixed Width>Next

With cursor draw your split lines so's you have 9 columns then Next

The first column will be selected(black highlight).

Hold SHIFT key and click on column 9 which selects all.

Column Data Format>Text and Finish.

You can do this with a formula............I think Biff posted one a while
back.

Search the google archives for splitting into multiple cells.


Gord Dibben MS Excel MVP

 

[Ragdyer]

With "010203040506070809" in A1, try this:

=IF(LEN($A1)<COLUMNS($A:A),"",MID($A1,2*COLUMNS($A:A)-1,2))

And copy across as needed.

Just places 2 characters in each column 'til you run out of data.