How to remove generic phone numbers

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

Regex

Accepted answers

Jonathan-Sherman

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

20191118 - RemovingPhoneNumbers.yxmd

All comments

AndrewS

hi @Xiahyl1

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

Regex.yxmd

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.

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335