Area Code Lookup

[John Cookson]

I had originally asked this on Friday and I appreciate the
responses I got, but i feel like I didn't explain myself
well enough to understand those responses. I will try
again.

In Column A I have all the US area codes from top to
bottom. 201, 202, 203, etc.

In Column B I have the State that corresponds to that area
code. So in A1 I have 201 and in B1 I have 'NJ'. This is
set up for all of the area codes in the country.

I want to be able to put a phone number, like '8745648759'
in D1 and in E1 have the State it corresponds to. It only
has to read the first three digits of the number. I know
this is done using VLOOKUP, but hopefully I explained
myself a little better.

Thanks for all you help.

 

[snflupigus]

Awesome, Im doing almost the EXACT same thing... help a bit

=VLOOKUP(LEFT(A3,3)*1,$Q$2:$R$394,2,FALSE)

I have to multiply by 1 my phone number field. why?

also, if i convert my phone field format to format as phone number i
does nothing until i 'f2' into the cell and 'enter' out.

why will these feilds not format without an edit? & why does my vlooku
not match the phone field until i multiply by one. even when they bot
fields i am matching LOOK the same, i get FALSE when i match them
there are no extra chars or anything either. whats the deal

 

[snflupigus]

o, also, i would like to write a VB function that receives a phon
number into the function and returns state. Can I store the values fo
all area codes and their corrosponding states (and in my case als
those corrosponding sales territories) in the function itself or will
have to have the phone/states/etc within the sheet somewhere for th
function to reference - not what i want though. I want it all containe
soley in a function.

I have start to write the function but am having trouble passing th
function a long and returning a string. My experience is with C++ so v
is a little rough for me learning syntax right now. Can you pass on
type and return another with vb w/in excel?

Thank

 

[Dave R.]

The reason for the *1 is because LEFT is a TEXT function, so in pulling the
area code off the TEXT 916-222-2222, the resulting 916 is "916". Multiplying
by 1 turns it into a number, and apparently (if it works) numbers are
contained in your lookup range, and not text.

If it ISN'T working, you can try removing the *1.. so if you have text in
your lookup range, try removing the *1 and see if that works.

 

[snflupigus]

It works if *1 IS there. it returns #NA if there is no * 1. I trie
using Trunc function as having it truncate 7 digits... this did no
work, does truncate simply cut off decimal places?

Again... I would like to write a function that contains an array tha
matches area code. that way I dont have to paste any thing into th
sheet except the new columns that have the functions in them. I don
want to have to paste in all the area codes etc.. tought function t
write or no

 

[Dave R.]

It works if *1 IS there. it returns #NA if there is no * 1. I tried
using Trunc function as having it truncate 7 digits... this did not
work, does truncate simply cut off decimal places?

Is there a problem or is this curiosity?

Again... I would like to write a function that contains an array that
matches area code. that way I dont have to paste any thing into the
sheet except the new columns that have the functions in them. I dont
want to have to paste in all the area codes etc.. tought function to
write or no?

It doesn't sound like that hard of a function to write, perhaps someone can
spring forward with a way to do it.

An easier option, though I could be horribly wrong, is to have some other
workbook with the table of area codes/states. That way you could just use a
formula to lookup the area code in the table in another, closed workbook.
That way you wouldn't have to enter a table into your worksheet.

Haven't tried it, don't know what's involved.

 

[snflupigus]

Well. Im just trying to clean up my functions so they're easier t
understand. having *1 adds clutter a bit. I guess i should just b
happy that it works and be done with it.

Function...

I have been messing with writing this function. - and am gettin
errors. Currently the data is within the workbook on a seperate sheet
This works alright. Probably best.

I cannot have it try to gather data from another sheet however becaus
multiple people over a network will need to work with this sheet
making sure the referenced closed workbook was always found would be
pain i bet.

I guess if it aint broke, and ive got everything working, i shouldn
keep trying to fix it.

Im just learning vb and excel and have to assume there is an easier wa
to do everything . Im pretty sure i could have done this sort of dat
processing it in about 30 minutes within a text and c++ program.

 

[Dave R.]

You can post back under functions or programming and maybe someone can help
you to write a short function that has a table stored within it, where you
could match text or numbers in a worksheet with some values in a table in
the function.

As for *1 adding clutter, you can also use --
which is multiplying by -1*-1 or 1, but doesn't have the '1' specifically in
it.

e.g. to pull a NUMERIC area code off 9162222323 use

=--LEFT("9162222323",3)

That is the more common way people convert a numeric text string to a number
Excel can compare to other true numbers.

 

[snflupigus]

If I were typing the phone numbers into the function i could just pu
them in quotes.... however, the phone number is column A., therefore
cant convert that way. I guess I've done it the only way possible.

Ill figure out the code sooner or later. I need to learn VB bette
anyway... thanks guys

 

[Dave R.]

I thought you were trying to use STATE(areacode) to return the
state/city/whatever automatically.

 

[snflupigus]

ideally thats what i will do.... once that function is written.
wish it looked as simple as State(a1)

so -- is the same as *1

I never knew that... but it does clean it up a tiny bit. interestin
trick -- , i cant find any info in Microsofts help on using it.

Thanks

 

[Dave R.]

If you go to Google groups into the Excel section, type in "unary minus" and
"coerce" and you should find a decent amount of reading material.