Data Cleaning

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

Workflow

Input

Regex

Output

Help

Accepted answers

apathetichell

Try the attached workflow

standardize phone number workflow.yxmd

All comments

apathetichell

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

Try the attached workflow

standardize phone number workflow.yxmd

Feras95p

@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

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703