Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA write macro change column with 3 number digits to 4 digits the | Excel Discussion (Misc queries) | |||
Header Insanity | Excel Discussion (Misc queries) | |||
How to customize number to 10 digits including 2 digits after deci | Excel Worksheet Functions | |||
Custom Number Formats in Code | Excel Programming | |||
change ten digits in a cell to include phone # formats | Excel Discussion (Misc queries) |