Formula help with phone number field 1

[aztech]

I need to create 3 groups based on specific area codes in California. For example, Group 1, will be called Northern California and will contain data based on area codes 707, 530, 916, 209. Group 2 will be called Los Angeles area and contain data based on area codes 805, 213, 323, 818, 562, 714 and Group 3 will be called Southern California and contain data based on area codes 858, 691, 760. Since the area code is not separate and is part of the entire phone #, how would I write this formula?

 

[synapsevampire]

Depends on the format of the phone number field.

Assuming (XXX) XXX-XXXX, use:

if mid({table.phone},2,3) in ["707", "530", "916", "209"] then
"Northern California"
else
if mid({table.phone},2,3) in ["805","213","323","818","562","714"] then
"Los Angeles"
else
if mid({table.phone},2,3) in ["858","691","760"] then
"Southern California"
else
"Unknown"

Adjust the MID function to allow for your area code within the field.

-k

 

[AndyMc]

If your area code starts in position 2, try:

stringvar areacode := mid({PhoneDatabaseField}, 2, 3);

select areacode
case "707", "530", "916", "209" : "Northern California"
case "805", "213", "323", "818", "562", "714" : "Los Angeles"
case "858", "691", "760" : "Southern California"
default : "Unknown

 

[aztech]

It's not working. Everything is coming up under "unknown". Our phone number format is xxx-xxx-xxxx

 

[AndyMc]

The mid function would be
mid({PhoneDatabaseField}, 1, 3)
for either of the solutions since the area code starts in posion 1

You could use
left({PhoneDatabaseField}, 3)
also

 

[aztech]

Thank you! That worked perfectly.

 

[synapsevampire]

Or you can use:

{PhoneDatabaseField}[1 to 3]

-k