Leading zeros being suppressed after splitting data in col

[nickdirect]

I have a column of data containing letter and number digits and I am trying to split off part of the number bit (the last few digits). The format of the column is 'text', as is the blank adjacent column to the right of it. I have used the 'text to columns' option to split the data but found that the split off data has all its leading zeros suppressed even though the format of the cell is 'text' and I have told 'text to columns' to treat the data as text. I am using Excel 2003 SP3. How can I fix this problem?

~

Nick
-------------------------

 

[GlennUK]

When you used the Text To Columns option, what data type did you give these results columns?

 

[nickdirect]

I changed the cell formats in the data and results columns from general to text to try to avoid the possibilty of getting problems but it still happens! If I type a number with a leading zero directly into the results column it displays as typed, but if I use 'text to columns to put a result into this column any leading zeros disappear.

~

Nick

 

[Peter_SSs]

nickdirect

Just to make sure, please check these step carefully:
1. In the Text to Columns wizard, at the last step just before you click 'Finish'
2. Look at the Data Preview section at the bottom and click in the column that contains the numbers. It should go black.
3. Now look at the column data format at the top right. It probably says 'General' and you need to change it to 'Text'
4. Now click Finish

This is the result for me: Original data is in column A. Data after text to Columns is in Columns C and D.

Mr Excel.xls
	A	B	C	D	E
1	SB123		SB	123
2	GY0023		GY	0023
3	CC0001		CC	0001
4	PG659		PG	659
5
Text to Columns

 

[nickdirect]

Thanks Peter

This is a very helpful answer and shows where I went wrong (I did not click the 2nd column in the last stage of the wizard to make to make the column go black). I had assumed that the whole of the original character string would be treated as text if I selected the 'text' option.

It all makes sense now.

~

Nick