Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to convert phone area-code to state name and time zone
I have a column with US phone numbers (all numerical, 10 digits). I would
like to have the State name in a column right next to the phone number, and the Time Zone in the next column to the right. I have the master Area Code, State, Time Zone data in any format needed: one long string, range, VBA constant... The obvious solution is to write a VBA that uses the 3 left-most digits and scans the master area-code table to find a match. The only problem is that my list of phone numbers has several 10,000 numbers and scanning the master area code again and again will take hours... Any advanced idea anyone? -- MF Boston |
#2
|
|||
|
|||
This is only an example:
Say in column A you have numbers like 123-456-7890, then in column B put =LEFT(A1,3) this would show as 123. Copy this formula down. You now have data the you can use VLOOKUP() on to get the items from you area code table -- Gary''s Student "NoYouShmoopie" wrote: I have a column with US phone numbers (all numerical, 10 digits). I would like to have the State name in a column right next to the phone number, and the Time Zone in the next column to the right. I have the master Area Code, State, Time Zone data in any format needed: one long string, range, VBA constant... The obvious solution is to write a VBA that uses the 3 left-most digits and scans the master area-code table to find a match. The only problem is that my list of phone numbers has several 10,000 numbers and scanning the master area code again and again will take hours... Any advanced idea anyone? -- MF Boston |
#3
|
|||
|
|||
NoYouShmoopie wrote:
I have a column with US phone numbers (all numerical, 10 digits). I would like to have the State name in a column right next to the phone number, and the Time Zone in the next column to the right. I have the master Area Code, State, Time Zone data in any format needed: one long string, range, VBA constant... The obvious solution is to write a VBA that uses the 3 left-most digits and scans the master area-code table to find a match. The only problem is that my list of phone numbers has several 10,000 numbers and scanning the master area code again and again will take hours... Any advanced idea anyone? Don't scan it again and again...? Do it once when you set up the file, then do it only for new numbers when you add them. Occasionally you may need to rescan the whole file as area codes get reassigned, but if you do that once a quarter or some such that's probably enough. Bill |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|