Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Extract Phone Numbers from Text Field

Status
Not open for further replies.

socalvelo

Technical User
Jan 29, 2006
128
US
CR 2016
SQL Server

I am extracting phone numbers from a text field. The phone numbers are in varying formats with spaces, dashes, slashes, etc.
ExtractString function works most of the time but sometimes there will not be a specific character following the phone number, so there would be a space before the next character.

99% of time it will look like this in the text field with a consistant use of slashes:
LOCATION:123 MAIN ST \PHONE:213/123-8902 \COMP:AT&T
I can easily handle this with the ExtractString formula

But what I am trying to solve, is when there is not an ending "\" after the phone number
\PHONE:213/123-8902
\PHONE:213/123-8902 ADDITIONAL TEXT


Can you use a space " " in an ExtractsString formula? I can't seem to get that to work

Here's my formula which works for most instances

if {Table.info} like "*\PHONE:*" then
ExtractString({Table.info}, "\PHONE:", "\")
else
if {Table.info} like "*\PH:*" then
ExtractString({Table.info} "\PH:", "\")
else
if {Table.Info} like "*\PNUM:*" then
ExtractString({Table.info}, "\PNUM:", "\")

Need to come up with something that catches some anomolies and when there is no "\" after the phone number.
If there are no more characters after the number then I can use the right function, but this doesn't hold true for everthing.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top