Area Code part 3

Thanks to everyone who helped me before, your suggestions
were perfect. I just need a little more help and i'll be
finished.

I now have a spreadsheet that can read a 9 digit phone
number, take the first three digits and spit out the US
State that corresponds to the three digit area code. I
have a list of every area code and it's State in columns A
and B.

Now what i need to do is when the area code is Texas, I
need the city along with the state. It is going to have
to read the first three digits to signal that it is Texas,
and then it's going to have to read the next 3 digits to
figure out the city. The formula I currently have for the
State lookup is =VLOOKUP(VALUE(LEFT(D1,3)),A:B,2,FALSE).

I need to add the Texas cities into my raw data, but how
can i get it so it recognizes the Texas area code and then
keeps reading the next three digits? Thanks guys

[This followup was posted to microsoft.public.excel.worksheet.functions
with an email copy to john c.
Please use the newsgroup for further discussion.]

OK, first of all, I'll assume you are working with 10 digit phone
numbers and not 9 as mentioned in your post.

Also, I'll assume the phone numbers are in col. F starting with F1.

Create a table with six digits (area code + exchange code) in column C
together with the city in column D. Now, use the untested formula
=VLOOKUP(VALUE(LEFT(F1,6)),C,2,FALSE).

Finally, if you store the city as 'city, state-abbreviation', you could
dispense with the state lookup altogether.

--
Trouble finding replies to your posts? Use a newsreader. See the
tutorial 'Outlook Express and Newsgroups' on my web site

Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , stratocaster9
@yahoo.com says...
Thanks to everyone who helped me before, your suggestions
were perfect. I just need a little more help and i'll be
finished.

I now have a spreadsheet that can read a 9 digit phone
number, take the first three digits and spit out the US
State that corresponds to the three digit area code. I
have a list of every area code and it's State in columns A
and B.

Now what i need to do is when the area code is Texas, I
need the city along with the state. It is going to have
to read the first three digits to signal that it is Texas,
and then it's going to have to read the next 3 digits to
figure out the city. The formula I currently have for the
State lookup is =VLOOKUP(VALUE(LEFT(D1,3)),A:B,2,FALSE).

I need to add the Texas cities into my raw data, but how
can i get it so it recognizes the Texas area code and then
keeps reading the next three digits? Thanks guys