Use REGEX Function to clean phone numbers

hartsie

Board Regular
Joined
May 6, 2020
Messages
77
Office Version
  1. 2016
I receive reports of structured data containing phone numbers in various phone formats. Because the input is free-form, and the phone numbers can come from any country, I want to remove all characters and spaces except the digits the user inputted originally.

Does anyone know of a VBA Function using REGEX that can pull out the phone number digits and ignore special characters and spaces?

For example, if the phone number is +(321) 155-6378, then I would want the output to be 3211556378. Sometimes the numbers are 61 32 1155 6378. Sometimes the numbers come through as 61321*155-6378... so I would want that to be 613211556378.

Does anyone know of a REGEX expression and the script in VBA that would pull only digits and neglect spaces or special characters (+ - ! ( ) )?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,972
Office Version
  1. 2016
Platform
  1. Windows
@VBE313 @Fluff @Rick Rothstein -- Where ever you all are employed, I am confident you are underpaid =D
I have been retired for some 18 years now so since my pension was calculated based on what I made back then, yeah, I guess I'm underpaid now. 🤪
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

hartsie

Board Regular
Joined
May 6, 2020
Messages
77
Office Version
  1. 2016
:D HA!

I would pay for responses like all of yours in this forum if I could.

Thank you, again.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,544
Office Version
  1. 365
Platform
  1. Windows
As I'm currently unemployed, definitely underpaid. 🙄
 

hartsie

Board Regular
Joined
May 6, 2020
Messages
77
Office Version
  1. 2016
@Fluff You are definitely underpaid. However, there is no shortage of appreciation here. I can't tell you how much your replies (you have replied to me before) and this forum has changed my approach to using excel. I try to hop on to help as many people as I can, but it appears I am usually unequipped to help with the unanswered problems.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
I am using Office '16. Do you know if TEXTJOIN is available in that version?
A quick way to find out is Excels intellisense (the version I use lacks the TEXTJOIN function ....)

ScreenShot123.png
 

Watch MrExcel Video

Forum statistics

Threads
1,129,510
Messages
5,636,755
Members
416,938
Latest member
sc58963

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top