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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,941
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.
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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
 

banneduser123

Banned - Rules violations
Joined
Mar 29, 2006
Messages
181
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...
 

banneduser123

Banned - Rules violations
Joined
Mar 29, 2006
Messages
181

ADVERTISEMENT

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

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623

ADVERTISEMENT

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.
 

banneduser123

Banned - Rules violations
Joined
Mar 29, 2006
Messages
181
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?
 

banneduser123

Banned - Rules violations
Joined
Mar 29, 2006
Messages
181
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
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,649
Messages
5,838,560
Members
430,555
Latest member
Schturman

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
Top