Re: Leading zeroes, text/numbers, import/export

From: Ellen Burd (anonymous_at_discussions.microsoft.com)
Date: 03/31/04 

Date: Wed, 31 Mar 2004 14:45:40 -0800

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

>-----Original Message-----
>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 wrote:
>>
>> 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 *** 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
>ec35720@msn.com
>.
>