How to convert phone area-code to state name and time zone

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
NoYouShmoopie said:
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?
Click to expand...

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
 
Back
Top