Someone please help

  • Thread starter Thread starter John Cookson
  • Start date Start date
J

John Cookson

I have a problem that i'm sure someone can help me with.

I have a list of every area code in the USA and it's
corresponding State. I want to be able to read a phone
number and assign the state.

For example : 2158945587 = PA

How do i set this up, and specifically it only needs to
read the first 3 digits of the phone number.

I would be God at work if i could do this. Thank you.
 
Hi John,

Use your list and use a Vlookup to return the state. If
the number is in A1. Enter something like this in A2:

=VLOOKUP(--MID(A1,1,3),F1:G1,2,0)

Biff
 
1. I'd name the table where the area codes and states are now stored.
AreaCodeTable.

2. Place the phone number in Cell D1.

3. Place this formula in E1.
=VLOOKUP(LEFT(D1,3),ZipCodeTable,2,FALSE)

4. The state should now be visible in Cell E1.

HTH
Paul
 
I don't mean to seem nit picky, but it might work better with one small
alteration:

1. I'd name the table where the area codes and states are now stored.
AreaCodeTable.

2. Place the phone number in Cell D1.
3. Place this formula in E1.
=VLOOKUP(LEFT(D1,3),AreaCodeTable,2,FALSE)

4. The state should now be visible in Cell E1.


In your formula, you used a ZipCodeTable instead of AreaCodeTable. If the OP
does a cut n paste it wouldn't work. This is actually the way I was going to
answer, but then I saw that you already had it.
 
As written this will return #N/A. The *TEXT* function Left
returns a *TEXT* value. You need to force a numeric return
value by using the -- unary operator.

Biff
-----Original Message-----
I don't mean to seem nit picky, but it might work better with one small
alteration:

1. I'd name the table where the area codes and states are now stored.
AreaCodeTable.

2. Place the phone number in Cell D1.
3. Place this formula in E1.
=VLOOKUP(LEFT(D1,3),AreaCodeTable,2,FALSE)

4. The state should now be visible in Cell E1.


In your formula, you used a ZipCodeTable instead of AreaCodeTable. If the OP
does a cut n paste it wouldn't work. This is actually the way I was going to
answer, but then I saw that you already had it.


--------------------------------------------------------- ------------------ ------------------

.
Click to expand...
 
Back
Top