ZIP Code insanity - , How to€¦vlookup, formats, number of digits.
 

What is the deal with Excel and ZIP codes, I know a lot of people out there
are using Excel for address lists as I do. Whenever I have a straightforward
ZIP lookup or match it turns into this big project figuring out what format
the ZIP from one file is and making the other file look like it. One is text
the other a number, one is missing the leading zero one looks like it has the
zero but is formatted that way, I go nuts. If you been there you know.

Can anyone tell me, €˜this is it, do it like this, it works every time. ? Do
I need a macro, combination of funtions, what am I missing? Thanks you guys.

ZIP Code insanity - , How to€¦vlookup, formats, number of digits.
 

Maybe a formula that checks both text and number matches.

=if(iserror(match(text(A1,"00000"),Sheet1!A:A,0)), match(A1*1,Sheet1!A:A,0),match(text(A1,"00000"),Sh eet1!A:A,0))

Same approach with vlookup.

If you have mixed 5 digit and 9 digit with or without hypens, it would be
more complex.

--
Regards,
Tom Ogilvy


"Doug929" wrote:

What is the deal with Excel and ZIP codes, I know a lot of people out there
are using Excel for address lists as I do. Whenever I have a straightforward
ZIP lookup or match it turns into this big project figuring out what format
the ZIP from one file is and making the other file look like it. One is text
the other a number, one is missing the leading zero one looks like it has the
zero but is formatted that way, I go nuts. If you been there you know.

Can anyone tell me, €˜this is it, do it like this, it works every time. ? Do
I need a macro, combination of funtions, what am I missing? Thanks you guys.

ZIP Code insanity - , How to€¦vlookup, formats, number of digi
 

Thanks Tom, I was hoping you would look at this. I was thinking more like
running a macro on each column of ZIPs to basically 'reset' them to a commom
format. I am looking at this right now, The two ZIP columns seem to look the
same, same length, leading zeros all that, I am using a vlookup and they do
not match. Looking at Excel help it says to format both to Text and then hit
F2 and then enter on the cell. Well there you have it, after I hit F2 the
vlookup works. So first, f2 is basically edit right? I edit then enter which
goes to the next cell, what am I doing? How can I do it automatically with a
macro.

Thanks again for taking the time, in case you don't know you have been
helping me for years now, hope I am not abusing it but sometimes I need the
help.


"Tom Ogilvy" wrote:

Maybe a formula that checks both text and number matches.

=if(iserror(match(text(A1,"00000"),Sheet1!A:A,0)), match(A1*1,Sheet1!A:A,0),match(text(A1,"00000"),Sh eet1!A:A,0))

Same approach with vlookup.

If you have mixed 5 digit and 9 digit with or without hypens, it would be
more complex.

--
Regards,
Tom Ogilvy


"Doug929" wrote:

What is the deal with Excel and ZIP codes, I know a lot of people out there
are using Excel for address lists as I do. Whenever I have a straightforward
ZIP lookup or match it turns into this big project figuring out what format
the ZIP from one file is and making the other file look like it. One is text
the other a number, one is missing the leading zero one looks like it has the
zero but is formatted that way, I go nuts. If you been there you know.

Can anyone tell me, €˜this is it, do it like this, it works every time. ? Do
I need a macro, combination of funtions, what am I missing? Thanks you guys.

ZIP Code insanity - , How to€¦vlookup, formats, number of digi
 

do the action you describe changes the cell you edited to a number - so if
the vlookup began working, it means it was probably previously Text.

you can test with

=istext(A1)
or
=isnumber(a1)

You can convert such a column by selecting it, format the column as General
or a custom numberformat like 00000

then do Data=Text to columns, and select delimited then OK click finished

--
Regards,
Tom Ogilvy


"Doug929" wrote:

Thanks Tom, I was hoping you would look at this. I was thinking more like
running a macro on each column of ZIPs to basically 'reset' them to a commom
format. I am looking at this right now, The two ZIP columns seem to look the
same, same length, leading zeros all that, I am using a vlookup and they do
not match. Looking at Excel help it says to format both to Text and then hit
F2 and then enter on the cell. Well there you have it, after I hit F2 the
vlookup works. So first, f2 is basically edit right? I edit then enter which
goes to the next cell, what am I doing? How can I do it automatically with a
macro.

Thanks again for taking the time, in case you don't know you have been
helping me for years now, hope I am not abusing it but sometimes I need the
help.


"Tom Ogilvy" wrote:

Maybe a formula that checks both text and number matches.

=if(iserror(match(text(A1,"00000"),Sheet1!A:A,0)), match(A1*1,Sheet1!A:A,0),match(text(A1,"00000"),Sh eet1!A:A,0))

Same approach with vlookup.

If you have mixed 5 digit and 9 digit with or without hypens, it would be
more complex.

--
Regards,
Tom Ogilvy


"Doug929" wrote:

What is the deal with Excel and ZIP codes, I know a lot of people out there
are using Excel for address lists as I do. Whenever I have a straightforward
ZIP lookup or match it turns into this big project figuring out what format
the ZIP from one file is and making the other file look like it. One is text
the other a number, one is missing the leading zero one looks like it has the
zero but is formatted that way, I go nuts. If you been there you know.

Can anyone tell me, €˜this is it, do it like this, it works every time. ? Do
I need a macro, combination of funtions, what am I missing? Thanks you guys.