Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Data Cleaning

Feras95p
8 - Asteroid

Dear all, 

 

I have one column which contains more than 67,000 mobile phone numbers of the customers but the format is not correct. 

 

What I'm looking for is to uniform the format for all contact as shown in the picture below:

 

So, as shown the correct number is contains 9 digits starts from 5 or 10 digits start from 05 or 12 digits starts from 9665

 

I want all the phone numbers to be re-formatted to be started 9665 and get rid of the numbers which are not started by 05 or 5 or 966 or the digits more than the correct number.

 

 

Any idea how it can be done?

 

 

Thanks in advance for your kind support 

k.PNG 

4 REPLIES 4
apathetichell
18 - Pollux

your problem is that phonenumbers are strings - not numbers you can do what you are trying to do by doing something like "966"+regex_replace(tostring([yourfield]),".*(\d{9})$","$1") but there are a variety of different ways. If you need help - past a text field or an excel file. not a screen grab.

apathetichell
18 - Pollux

Try the attached workflow

Feras95p
8 - Asteroid

@apathetichell  Great! It works perfect, only thing is remaining is that I want to filter if the mobile number after the re-foramtting is 12 digits true less than or more than 12 digits that mean the mobile phone is not correct so keep it in the false output from the filter 

apathetichell
18 - Pollux

length([field]=12 in a filter tool - after you do the conversion and where 12 is whatever I called the phone number field. It's alreayd a string.

Labels