Leading zeroes, text/numbers, import/export

[Ellen Burd]

Using Excel 2002 (soon 2003) on XP Pro

Can anyone steer me to a good explanation of
importing/converting/comparing/exporting files that
contain numbers that have leading zeroes -- or should
have, but don't?

We frequently work with ID numbers that should contain
leading zeroes. They come from multiple sources,
including:

~ text files
~ other Excel files
~ Access files
~ within the company (we have some control)
~ external sources (we have no control)

We import into Excel and sometimes later export to
Access.

The IDs tend to be in different formats from the various
sources:

'009999 (text)
009999 (text)
9999 (text)
9999 (number)
009999 (custom format)

We need to be able to convert these IDs to a standard
format that will export/import consistently so that we
can compare them from one list or sheet to another, print
them in reports, etc.

The custom format doesn't seem to work well, because the
user I'm helping can't always tell that that's what it
is. Then, when comparing to other lists or exporting,
there's no match or the zeroes don't export.

I usually convert to plain text (009999 no apostrophe)
using If statements, concatenation, etc. However, it's
time-consuming to figure out the current format, insert
columns, apply the functions, copy the functions, paste
special values, and delete the old data every time.

I could probably write a macro that would help speed the
process if I would just take the time.

BUT -- This seems like it must be a common issue. It
seems like Excel must have an easier solution that I just
haven't run across yet.

Thank you for your help!

 

[Dave Peterson]

Just some commiserating.

I think that this is a training issue. I know that my rules for "numeric"
fields has evolved into:

If I want to do arithmetic, use a numeric format.

If it's some sort of ID, then use Text.

So when I import a text file or create a file for myself, I can be consistent.

But the bad news is you can be as consistent as you want--but that won't help
you much with the data given to you by other groups.

I'd invest that time for the conversion macro if I were you.

But I'd only write it to go from numbers to text.

For text to numbers, I'd select an empty cell, copy it, and select my range and
do Edit|PasteSpecial|check Add.

This'll coerce the text numbers to number numbers.


Or if you're in a hurry and want to match your values to the table:

=vlookup(--a1,sheet2!a:e,3,false)

-a1 will convert the "Text" value to number and the second negative will change
it back to its original sign.

or even:

=vlookup(text(a1,"000000"),sheet2!a:e,3,false)

to make your number numbers, 6 character text numbers.

 

[Ellen Burd]

Hi Dave,

Thanks for your comments and for sharing my misery.

It just seems like there ought to be an easier way ...

I guess I'm just going to have to do some major research
and figure it all out for myself. I haven't really taken
the time to fully understand what happens in all
circumstances and what can be compared successfully to
what, using functions.

Thanks again.
Ellen