Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
NoYouShmoopie
 
Posts: n/a
Default 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   Report Post  
Gary''s Student
 
Posts: n/a
Default

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   Report Post  
Bill Martin
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 01:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"