identify time zone by looking at area code

banneduser123

Banned - Rules violations
Joined
Mar 29, 2006
Messages
181
I have a column full of phone numbers. I would like to be able to denote what time zone each number is in, in a separate column for each corresponding row.
How can I do this? I would imagine the formula would look at the first 3 digits for each number and then be able to identify what time zone it is…

Thanks a lot…
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Do you have a table of what area codes are in what time zones, if not, you would need to create that table somehow because Excel does not know time zones for area codes by itself.
 
Upvote 0
I have a column full of phone numbers. I would like to be able to denote what time zone each number is in, in a separate column for each corresponding row.
How can I do this? I would imagine the formula would look at the first 3 digits for each number and then be able to identify what time zone it is…

Thanks a lot…
Do you have a table that shows what the time zones are for the area codes?

If so, then you can do a lookup.

See if this helps:

http://contextures.com/xlFunctions02.html
 
Upvote 0
well, i have a paper list of all the area codes and the time zones for each, i can type that up in some form...
 
Upvote 0
think you can write out the formula for me and how i should enter the area codes and time zones i do have please?
 
Upvote 0
think you can write out the formula for me and how i should enter the area codes and time zones i do have please?
What do your phone numbers look like?

Ideally, you'd want the phone numbers in the lookup table to be in the same format as the phone numbers you already have.

Post a few examples.

i have a paper list of all the area codes and the time zones for each, i can type that up in some form...
If you do a web search you might be able to find a site that has this information already in a list format that you can copy and paste into Excel.
 
Upvote 0
so let's say i have a column of phone numbers in Column A:

973.555.4986
204-555-2643
206.555.5172


then i have a list of the area codes denoting what time zone in another worksheet:
973 = Est
204 = Cst
206 = Pst



basically what i would like is for whenever i add a phone number in column A, it will automatically tell me what time zone it is in by inputting the data into Column B

can you please write up a formula to show me this?
 
Upvote 0
actually check that, right now my excel file looks like this:

worksheet 1:

in column A the phone numbers

973.555.4986
204-555-2643
206.555.5172


in worksheet two:
in column A:
973
204
206

in column B

EST
CST
PST
 
Upvote 0
so let's say i have a column of phone numbers in Column A:

973.555.4986
204-555-2643
206.555.5172


then i have a list of the area codes denoting what time zone in another worksheet:
973 = Est
204 = Cst
206 = Pst



basically what i would like is for whenever i add a phone number in column A, it will automatically tell me what time zone it is in by inputting the data into Column B

can you please write up a formula to show me this?
Create this 2 column table:

973...Est
204...Cst
206...Pst

Let's assume that table is in the range F2:G4.

A2:A4 = phone numbers:

973.555.4986
204-555-2643
206.555.5172

Enter this formula in B2 and copy down to B4:

=VLOOKUP(--LEFT(A2,3),F$2:G$4,2,0)
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top