| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to cnvrt Phone to State w/out cell ref
I am trying to write a function to convert a phone number to state..
Currenly I have using vlookup and it works fine. here is what Im usin now... =VLOOKUP(LEFT(A2,3)*1,State,2,FALSE) State is a named range in a seperate sheet within the workbook. A:A are phone numbers/account numbers like 2015552222 I would like to get rid of the sheet containing the cells i referenc with vlookup and simply build a function with an array that contain all area codes and the states they're in. I also want to write another function that then converts states int sales regions. West, Midwest, East, etc.... I have failed horribly at trying to do this... please help. What I doing works now, but i want the functions so it is easier for other not so good with excel to process the data we already have on thei own. Thank -- Message posted from http://www.ExcelForum.com |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to cnvrt Phone to State w/out cell ref
|
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function to cnvrt Phone to State w/out cell ref
No need to use VBA.
In cell B2, go Insert, Name, Define and in the refers to box type in: =VLOOKUP(LEFT(A2,3)*1,State,2,FALSE) Then in names in workbook type in a function name of your choice e.g. actostate Click OK In cell B2, enter the formula =actostate In cell C2, enter the formula =actostate, etc. (Assumes that values in column to left of formula contain area code) snflupigus < wrote: I am trying to write a function to convert a phone number to state.. Currenly I have using vlookup and it works fine. here is what Im using now... =VLOOKUP(LEFT(A2,3)*1,State,2,FALSE) State is a named range in a seperate sheet within the workbook. A:A are phone numbers/account numbers like 2015552222 I would like to get rid of the sheet containing the cells i reference with vlookup and simply build a function with an array that contains all area codes and the states they're in. I also want to write another function that then converts states into sales regions. West, Midwest, East, etc.... I have failed horribly at trying to do this... please help. What Im doing works now, but i want the functions so it is easier for others not so good with excel to process the data we already have on their own. Thanks --- Message posted from http://www.ExcelForum.com/ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| replace state names with state code abbreviations | Excel Worksheet Functions | |||
| Associating a STATE or TIME with a phone number | Excel Worksheet Functions | |||
| How can I show state-by-state data (as silos) on a map of NA | Charts and Charting in Excel | |||
| auto fill state and city from phone number? | Excel Discussion (Misc queries) | |||
| How to convert phone area-code to state name and time zone | Excel Discussion (Misc queries) | |||
Hybrid Mode
