TEXT TO COLUMNS WITH LEADING ZEROS
 

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?

Answer: TEXT TO COLUMNS WITH LEADING ZEROS
 

Yes, there is a way to split a numerical string into several columns and retain leading zeros. Here's how you can do it:

1. Select the column that contains the numerical string you want to split.
2. Click on the "Data" tab in the ribbon.
3. Click on "Text to Columns" in the "Data Tools" group.
4. In the "Convert Text to Columns Wizard" dialog box, select "Delimited" and click "Next".
5. In the next screen, select the delimiter that separates the numbers in your string (e.g. comma, space, etc.) and click "Next".
6. In the next screen, select "Text" as the column data format and click "Finish".
7. Now, the numerical string has been split into several columns, but the leading zeros are still missing.
8. To retain the leading zeros, you need to format the columns as "Text". To do this, select the columns that contain the numbers with leading zeros, right-click on them, and select "Format Cells".
9. In the "Format Cells" dialog box, select "Text" as the category and click "OK".
10. Now, the leading zeros should be retained in the columns.

That's it! You have successfully split a numerical string into several columns and retained leading zeros.

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.

---
Regards,
Norman



"Peggy" wrote in message
...
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?

TEXT TO COLUMNS WITH LEADING ZEROS
 

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

"Norman Jones" wrote:

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.

---
Regards,
Norman



"Peggy" wrote in message
...
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?

TEXT TO COLUMNS WITH LEADING ZEROS
 

Assuming you have a text string in A1 of 010203040

DataText to ColumnsFixed WidthNext

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 FormatText 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


On Wed, 10 Jun 2009 14:36:01 -0700, kesse
wrote:

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

"Norman Jones" wrote:

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.

---
Regards,
Norman



"Peggy" wrote in message
...
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?

TEXT TO COLUMNS WITH LEADING ZEROS
 

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.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"kesse" wrote in message
...
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

"Norman Jones" wrote:

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.

---
Regards,
Norman



"Peggy" wrote in message
...
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?

I don't know if this is still in need of resolving, but one thing to note is that in step 3, you need to actually select all of the columns in the preview and change each column to text. If you just select the text option, it will only change the first column to text but any remaining columns will remain under General.