Keeping leading zeros when using Text-to-Columns

  • Thread starter Thread starter Ember
  • Start date Start date
E

Ember

Hi all,

I'm using Excel X for Mac, and I'm trying to split my four-digit dat
into two rows with two digits each:

0001 => 00|01
0101 => 01|01
9800 => 98|00

When I first imported the data it dropped all the leading zeros, bu
this was quickly remedied by making the custom format: 0000

However, if I go straight from here to text-to-columns the zeros don'
show up in the fixed-width option screen, making it impossible t
properly separate them.

Likewise, if I try to set the formatting of the cells to text, all th
leading zeros are instantly dropped.

The latter attempt - switching the formatting to text, was what I ha
understood I needed to do from reading other posts. Perhaps thi
doesn't work on Excel for Mac?

Any help appreciated, thanks
 
Did you really mean two columns (not rows)?

If you already imported your data, you could use a couple of helper
cells/columns with formulas like:

=INT(A1/100)
and
=MOD(A1,100)

Is your text file fixed width? If yes, then if your text file is named .csv,
then rename to .txt. Then when you open the file, you should see the text to
columns wizard. You can draw a line between the hundreds and tens digits and
choose text for each field.

(Well, I can do it in xl for windows.)
 
Thanks very much for the reply. I'm away from the computer with the dat
now, but I will try your solutions when I get the chance
 
Back
Top