leading zeros

[Marilyn Fulper]

Someone sent me an Excel file which contains several columns
including one with identity numbers and one with only the last four
digits of the identity numbers. I don't need the column with the
entire numbers, only the one with the last 4 digits for each entry.
Deleting the column with the entire numbers destroyed the column with
the last 4 digits beause apparently a forumla was used to pull out the
last four digits. I tried editing the column with entire numbers by
deleting the numbers I did not need but that created another problem.
Instead of getting a four digit number, I ended up with some 3 digit
numbers if the first number was zero. For example, 0321 shows up as
321. I tried adding the zeros myself, but they disappear. I tried
formating those cells by following the directions in "help" . That
works as long as I keep it in Excel, but when I save as tab delimited
text, the zero's disappear again. I have to save as text to import it
as a batch file into another program.

How can I add the zeros and save as text.

Thanks,
Marilyn
(e-mail address removed)

 

[J.E. McGimpsey]

Someone sent me an Excel file which contains several columns
including one with identity numbers and one with only the last four
digits of the identity numbers. I don't need the column with the
entire numbers, only the one with the last 4 digits for each entry.
Deleting the column with the entire numbers destroyed the column with
the last 4 digits beause apparently a forumla was used to pull out the
last four digits. I tried editing the column with entire numbers by
deleting the numbers I did not need but that created another problem.
Instead of getting a four digit number, I ended up with some 3 digit
numbers if the first number was zero. For example, 0321 shows up as
321. I tried adding the zeros myself, but they disappear. I tried
formating those cells by following the directions in "help" . That
works as long as I keep it in Excel, but when I save as tab delimited
text, the zero's disappear again. I have to save as text to import it
as a batch file into another program.

How can I add the zeros and save as text.

Couple of suggestions:

1) Formatting doesn't affect the underlying value of a cell. But
when you save as Text, the displayed format will be output, so if
you choose Format/Cells/Number/Custom "0000" your text file will
contain the leading zeros (but see caveat below).

2)If you want to change the XL file and your original column is
still intact:

Select the column. Choose Data/Text to Columns, and select the Fixed
radio button. Click Next. Click in the textbox to the left of the
fourth digit from the right - a line will appear where the split
will be made. Click Next. Select the first column in the textbox and
click the Do not import radio button. Select the second column and
click the Text radio button. Click Finish.

That may look complicated, but it's easy after you go through it
once.

3) If the original column is gone and you have 1-4 digit numbers:

In an empty column, enter

B1: =TEXT(A1,"0000")

drag/copy down as far as necessary. Copy the column. Select column
A. Choose Edit/Paste Special and select the Values radio button.

Caveat: if you read the text file back into XL, XL's parser will
strip the leading zero (unless you choose Data/Import Text file and
specify the column should be Text on the third tab).