Leading zeros being suppressed after splitting data in col

nickdirect

New Member
Joined
Apr 7, 2006
Messages
5
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
-------------------------
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
When you used the Text To Columns option, what data type did you give these results columns?
 

nickdirect

New Member
Joined
Apr 7, 2006
Messages
5
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,513
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1SB123SB123
2GY0023GY0023
3CC0001CC0001
4PG659PG659
5
Text to Columns
 

nickdirect

New Member
Joined
Apr 7, 2006
Messages
5
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
 

Forum statistics

Threads
1,141,018
Messages
5,703,754
Members
421,313
Latest member
Mooncake1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top