How do I extract the first three Numeric characters from a field?

[OKCMikeSoll]

I am trying to extract the area code from the phone number in our contacts table. The problem is that due to poor data entry, the field does not have a standard format. We have told our tech's to imput the phone number in the format XXX-XXX-XXXX but of course it is not always done correctly.

Is there some formula that will extract the first three numeric characters from the phone number field it would have to ignor characters like slashes or spaces?

Any assistance would be most appreciated.

 

[dgillz]

Are there ever any letters in these strings? Or is it all numbers and special characters only?

If so there is a great UFL that takes all punctuation out of a string available on Ken Hamady's website. It literally removes anything that is not a letter or a number from the string.

So if there are no letters, you could apply this UFL to the field and then do a left,3 on that result.

http://www.kenhamady.com/bookmarks.html

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[synapsevampire]

Relying upon data entry people to enter data in a proper format is a bad idea, typically the result of an inexperienced programming staff.

You should be performing validation on what's entered.

To work around this, you can use the following code:

whileprintingrecords;
Stringvar AreaCode:="";
numbervar Counter;
For Counter := 1 to len(trim({table.phone})) do(
if not(isnull({table.phone}))
and
len(AreaCode) < 3
and
isnumeric(mid({table.phone},Counter,1)) then
AreaCode:=AreaCode+mid({table.phone},Counter,1)
);
AreaCode

-k

 

[OKCMikeSoll]

This field is may sometimes contain letters as well as spaces infront of the phone number. Here is an example of formatting of stored numbers:

(555)5551234
555.555.1234
555)-555-1234
(555 )-555-1234

Basically there is no standard. If the field has numbers 9 Numbers in it regardless of the formatting, then I would like it to give me just the first three.

 

[synapsevampire]

Interesting idea, dgillz, that is a simple formula though, no need for a UFL install:

whileprintingrecords;
Stringvar Output:="";
numbervar Counter;
For Counter := 1 to len(trim({table.phone})) do(
if not(isnull({table.phone}))
and
isnumeric(mid({table.phone},Counter,1)) then
Output:=Output+mid({table.phone},Counter,1)
);
Output

-k

 

[dgillz]

Use SV's formula. Very nice formula SV.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[synapsevampire]

A slight tweak for your NEW requirements, and note that you want 10 not 9 numbers:

whileprintingrecords;
Stringvar Input:= " ( 999- 123 4321 )";
Stringvar Output:="";
numbervar Counter;
For Counter := 1 to len(trim(Input)) do(
if isnumeric(mid(Input,Counter,1)) then
Output:=Output+mid(Input,Counter,1)
);
if len(Output) = 10 then
left(Output,3)
else
"N/A"

To display the entire number properly use:

picture(Output,"(XXX) XXX-XXXX")

-k

 

[OKCMikeSoll]

Thank you synapse, it worked perfectly!