Parse Phone

[dcarguy]

OK..hope this is the right area. I have a text field with a bunch of text. I am trying to extract a valid US telephone number from the field.

It is fairly easy with php and mysql but I can't seem to find the magic bullet for filemaker. The usual variables are XXX.XXX.XXXX OR (XXX) XXX-XXXX

and so on.

IE:

"1971 tandem grain truck with a 20 foot box and 427 motor air brakes runs well call 402-768-3486"

or

"71 Chevy pickup was restored from ground up in 2002. It is a pearl blueish/purple Has a brand new rebuilt GM goodwrench 350 crate motor. It has a rebuilt turbo 350 Transmission. It has a total of 7 inches of lift with 35x12.50x15 super swampers. I relocated the fuel tank form behind the seat to the frame. Its approximately a 30 gallon tank. This truck has alot of new parts. Has a Clarion Cd player. I put a electric fan for the radiator to gain a few horsepower. Its a Great truck. It was the first vehicle i owned and don't want to get rid of it but have to. can call me at 402 841 8145"

Thanks for any help you can provide. This board has been invaluable to me.

Shane

[comment]

Try something like this for starters:

Let ( [

txt = TrimAll ( Substitute ( text ; [ "." ; " " ] ; [ "-" ; " " ] ; [ "(" ; " " ] ; [ ")" ; " " ] ) ; 0 ; 0 ) ;

mask = Substitute ( " " & txt & " " ; [ "0" ; "#" ] ; [ "1" ; "#" ] ; [ "2" ; "#" ] ; [ "3" ; "#" ] ; [ "4" ; "#" ] ; [ "5" ; "#" ] ; [ "6" ; "#" ] ; [ "7" ; "#" ] ; [ "8" ; "#" ] ; [ "9" ; "#" ] ) ; 

pos = Position ( mask ; " ### ### #### " ; 1 ; 1 )

] ; 

Case ( pos ; Middle ( txt ; pos ; 12 ) )

)

This may need additional adjustments to deal with the "and so on" part.

[dcarguy]

THANK YOU!! I greatly appreciate your help!!

Shane