Home » Category » Microsoft Excel

Microsoft Excel: ZIP Code insanity - , How to…vlookup, formats, number of digits.

204| Sat, 31 May 2008 22:14:00 GMT| doug929| Comments (3)
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.

Keywords & Tags: zip, code, insanity, to…vlookup, formats, number, digits, microsoft, excel

URL: http://www.developertags.com/microsoft-excel/495082/
 
«« Prev - Next »» 3 helpful answers below.
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.
>
>

tomogilvy | Sat, 31 May 2008 22:15:00 GMT |

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:
[vbcol=seagreen]
> 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:

doug929 | Sat, 31 May 2008 22:16:00 GMT |

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:
[vbcol=seagreen]
> 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:

tomogilvy | Sat, 31 May 2008 22:17:00 GMT |

Microsoft Excel Hot Answers

Microsoft Excel New questions

Microsoft Excel Related Categories