Phone Number Formatting Question

[#1]

I'm importing phone numbers that have no standard formating.  Some have (XXX), some have +999, some have "-", etc.  I found the purgeChr() function, which helps immensely.  However, many of the numbers have extensions, such as....

1234567890ext999 or

1234567890x444

Is it possible to remove everything after (and including) the "e" or the "x" in the examples above?

Thanks,    Dan

[Anonymous]

Heh, a familiar problem ...  see this (original phone number here is field P):

// Phone
if(index(lower(P), 'x')>0,
// there is extension
if(len(keepchar(subfield(lower(P),'x',1),'0123456789'))=10,
  //format with extension
  '(' & left(keepchar(subfield(lower(P),'x',1),'0123456789'),3) & ')' &
  ' ' & mid(keepchar(subfield(lower(P),'x',1),'0123456789'),4,3) &
  '-' & right(keepchar(subfield(lower(P),'x',1),'0123456789'),4) &
  ' x ' & subfield(lower(P),'x',2),
  // leave data as is
  P),
// there is no extension
if(len(keepchar(P,'0123456789'))=10,
  // format without ext
  '(' & left(keepchar(P,'0123456789'),3) & ')' &
  ' ' & mid(keepchar(P,'0123456789'),4,3) &
  '-' & right(keepchar(P,'0123456789'),4),
  // we'll see
  P))
   as "Work Phone",
//

[Colin-Albert]

Subfield, keepchar, purgechar and replace will be useful functions to extract the data you need.

[MarcoWedel]

Hi,

you could also use regualr expressions for this task.

Here are some links on using RegEx in QlikView:

• http://www.qlikfix.com/2010/10/18/regular-expressions-in-the-load-script/  by Barry Harmsen
• How to use regular expressions by Andrea Ghirardello

hope this helps

regards

Marco

[jagan]

Hi,

You can use keepchar() like below

LOAD

*,

KeepChar(PhoneNumber, '1234567890exEX') AS PhoneNumber_Formatted

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

[PrashantSangle]

Hi,

If possible post sample phone numbers with expected output.

Use combination of subfield(),KeepChar() and if-else.

Regards

[#7]

HI Margus,

If your phone number is first 10 digits then why don't you use LEFT(PhoneNumber,10) as PhoneNumber?

Thanks,

Vamsi

[#8]

Hi Dan Magnus,

i got solution for your requirement,Used sub field function in script.

Please find attached QVW.

Thanks,

Vamsi

[#9]

So the main formula I'm using is:   PurgeChar(HomePhone,'()-.,:+/=|_~?*` '))

This is getting rid of the symbols I'm seeing.   However, now I need to get rid of all alpha (letters) I'm seeing, except for E and X (for phone extensions...I need these letters to use the index function).    Is there a way, besides listing each of the 24 letters in the formula above, to eliminate these letters?

Thanks.

[rwunderlich]

How about turning it around and using keepchar()?

KeepChar(HomePhone,'0123456789EX')

-Rob