Text to Columns - Can't change destination fields to text

[DavidBroadus]

When separating telephone numbers to the appropriate format, I change the destination cells in the spreadsheet to 'text', and also change the destination fields to 'text' in the 'text to columns' wizard. The separated numbers, however, still drop leading zeros. How can I retain the leading zeros in the destination columns?

 

[ace19852]

Hi and welcome to the board. I assume the 1st column has the original number and B1 has the number with the missing zeros after splitting to columns

Then let say you insert a new blank column C and could put in C1 something like this

=RIGHT(TEXT(B1,"0000"),3)

Now the more zeros you put in fill the beginning of the cell with zeros in this case 3 zeros before the number starts. Then I assumed that field always has only 3 digits hence the 3

You will need to play around with the number of zeros and the 3 to suit your needs

Cheers

 

[Norie]

If you specify Text as the column data format then you shouldn't lose leading zeroes, and you shouldn't need to format the destination cells to Text.

Is the data delimited or fixed width?

If it's the former what are you using for the delimiter?

Can you post small set of sample data?

 

[DavidBroadus]

If you specify Text as the column data format then you shouldn't lose leading zeroes, and you shouldn't need to format the destination cells to Text.

Is the data delimited or fixed width?

If it's the former what are you using for the delimiter?

Can you post small set of sample data?

Hi Norie,

ORIG
100200
100300
100400
100500
100600
SPLIT
10	200
10	300
10	400
10	500
10	600
All columns are text. Split was made with 'fixed' criteria: 2 digits/4 digits. As you can see in column B, 0 is missing. (Sorry if I'm not responding in the correct manner - my first time on here) Dave

<tbody>
</tbody>

 

[DavidBroadus]

Hi Ace,

Thanks very much for that! Your formula takes care of the problem. Still curious as to why it happens in the first place, but at least I don't have to change them all manually.

Regards,
Dave

 

[Norie]

Dave

When I carry out Data>Text to columns... and specify Text for the 2nd column (or both columns) the leading zero isn't lost.

I even get the warning from Excel that I have numbers stored as text.

 

[DavidBroadus]

Hhmmm...well, we all know how annoying it is when a problem is specific only to oneself, but I'll live with it for now, using Ace's workaround. Thanks, Norie!

 

[MAHMOUD-LEE]

Hi,Dave
To separate the left number
This formula can be used
=LEFT(A1;FIND(REPT("0";2);A1))*1
To separate the number right
This formula can be used
=RIGHT(A1;LEN(A1)-FIND("0";A1)-1)*1

 

[Norie]

It you want to use formulas use LEFT and RIGHT.

=LEFT(A1,2)

=RIGHT(A1,4)

If you don't know the no of digits at the end use MID.

=MID(A1,3, LEN(A1)

 

[DavidBroadus]

Thanks!