How to remove generic phone numbers

[Xiahyl1]

Hi everyone,

I need to clean up a phone number column, below is an example of the data. I need to remove spaces, dashes, leading and trailing spaces, letters, etc, which I did with the data cleansing tool just fine, but I also need to remove generic phone numbers such as 666666666 or 000000000, but not sure how to do that I tried to use regex but I am a newbie and could not figure out how to do it. Any Idea? 

Thanks.

Data example:

Client Id	Mobile Phone
1	646-285-6758
2	666666666
3	508-787-1000
4	212-207-2175
5	9-011-972-545-
6	914-844-6711,
7	505- 692- 9967
8	000000000
9	917000000
10	860  248  1457

[AndrewS]

hi @Xiahyl1 

I've attached a workflow which should do what you are looking for. I googled the regex and modified it.

[Xiahyl1]

Thank you AndrewS, that helped me, it removed the generic phone number formatting, but it doesn't remove for example 947000000 which is not a valid phone number, it removed the zeros but not the 947. It might sound a dumb question, but I am new to using regex and cannot figure it out.

Thanks again for your help.

[neilgallen]

This boils down to how to determine what a 'valid' phone number is.

In the situation above, you could parse out the area code from the remainder of the phone number and put the remaining 7 digit number through the same check as before, looking for a single digit repeatedly.

Keep in mind that this solution would only remove numbers that are "11111" "22222222" etc. Were someone to give you a pattern such as "123456789" it would pass as valid since the numbers don't repeat. You'd have to create a new scenario for that one.

[AndrewS]

Hi @Xiahyl1 , I agree with @neilgallen , you need to define a rule around what is considered a valid number.

I've amended the workflow with two additional options.

The first will tokenize the data and then provide a max count on the number of consecutive characters. You can then set your tolerance with a filter.

The second will strip the first characters out as mentioned by @neilgallen and then run the same regex against the substring.

Hope this helps.

[Xiahyl1]

Thanks very much for your ideas; it helped me figure out what I needed.

Using the example below, I was able to create 3 regexes that do what I need.

I would like to know if there's a way to combine the 3 to have just one; I tried but it does not work.

1st regex: \d{10,} (remove numbers with more than 9 digits)

2nd regex: ^(\d){0,8}$ (remove numbers with less than 9 digits)

3rd regex: (\d)\1{8,} (remove generic numbers like 666666666 for example)

Data example:

Client Id	Mobile Phone
1	646-285-6758
2	666666666
3	508-787-1000
4	212-207-2175
5	9-011-972-545-
6	914-844-6711,
7	505- 692- 9967
8	000000000
9	917000000
10	860  248  1457

[Jonathan-Sherman]

Hi @Xiahyl1,

You can put you're regex expressions inside a Regex_Match function in a filter tool to remove rows that meet each expression. All expressions could be pushed into one filter tool, however i would separate them out to try and reduce the load. If your first filter removes 100 rows this means your next filter has less work to do and so on. Therefore is more efficient in the long run, especially as string testing / manipulation can be very memory intensive. The most effective (removing the most records) should be the first filter, leaving others less and less to do so you may want to change the order of the filter tools.

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[Xiahyl1]

Hi Jonathan,

If I understand correctly, your workflow will remove the records, but I don't need to remove the record, I need to keep the record with the phone number space blanc.

For example, if I have: 

Client Id	Mobile Phone	Name
1	646-285-758	XYZ
2	666666666	ABC

It should return:

Client Id	Mobile Phone	Name
1	646-285-758	XYZ
2		ABC

I need to keep the record.

Thanks a lot 

[Jonathan-Sherman · Accepted solution]

Hi @Xiahyl1,

Sorry i misunderstood, i've attached an amended workflow to use the REGEX_Replace function. Which replaces any matched functions with an empty cell.

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[Xiahyl1]

Thank you very much Jonathan, it worked perfectly.