# identify time zone by looking at area code

#### banneduser123

##### Banned - Rules violations
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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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.

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

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...

think you can write out the formula for me and how i should enter the area codes and time zones i do have please?

You can do a VLOOKUP. Check it out in excel help.

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.

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?

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

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)

Replies
3
Views
360
Replies
2
Views
261
Replies
2
Views
243
Replies
3
Views
183
Replies
0
Views
474

1,221,493
Messages
6,160,139
Members
451,624
Latest member
TheWes

### 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.

### Which adblocker are you using?

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

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