Best Practices

Hi everyone!

I hope you are well and safe.

I want to clean the phone number. In some of phone numbers I have (02) or (03) or 03 or 02. I want to replace them with 61. In data I have some others extra strings and other unnecessary data. Could you please let me know how I can clean them. Also, I want to do a filter and find phone numbers that start with 61 or 02.

I have attached the sample file and ideal phone number.

I really appreciate your help.

Thanks,

Simon

Phone Number.xlsx

ideal Phone number.xlsx

Best Practices

Accepted answers

ArtApa

Hi @Simon1187 - I adjusted the workflow to do just that (02) -> 2 and Empty = Empty.

Data Cleanse Phone Number AA.yxmd

All comments

T_Willins

Hi @Simon1187,

Attached is a workflow that matches your ideal output. However, I don't know if this will work for your actual data set as there were no phone numbers starting with 02 and you indicate you want to replace 03 or 02 with 61, which would leave your data without any numbers starting with 02. The Filter tool would only need to be for numbers starting with 61 instead of how it is written to filter for both starting with 61 or 02.

The way I approached this was thinking through the rules based on the data.

First, remove letters, punctuation and extra whitespace with the Data Cleanse tool

Formula rules (each rule looks at remaining records that did not meet the previous rule):

1. If the number starts with 61 or 69 then leave the number as is

2. If the number starts with a zero and the next digits after any zeros was 61, then remove leading zeros

3. If the number starts with a zero and the next digits after any zeros was 69, then remove leading zeros

4. If the number starts with a zero, remove zeros and add 61 to the front

5. All remaining records add 61 to the front

The only record that didn't match after applying these rules was record #16, which had a 3 at the end of your Ideal Phone Number, but didn't appear to have a rule to apply.

Data Cleanse Phone Number.png

Data Cleanse Phone Number.yxmd

Simon1187

Hi @T_Willins ,

Thank you very much for your help.

In record 10 that starts with 61 I want to remove 0. I want to replace (02) 2. Also, in some cells that are empty it return 61.

Could you please let me know how I can do this.

Thanks,

Simon

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703