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 (+ - ! ( ) )?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
684
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))

Snag_e867b27.png
 

hartsie

Board Regular
Joined
May 6, 2020
Messages
77
Office Version
  1. 2016
@VBE313

I literally laughed out loud in my seat when I saw this. Great work! There is no way I would have thought of that on my own... that is very good.

Thank you.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,981
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

This function does not use RegExp, but it will return the digits only from any value passed to it...
VBA Code:
Function Digits(PhNum As String) As String
  Dim X As Long
  For X = 1 To Len(PhNum)
    If Mid(PhNum, X, 1) Like "[!0-9]" Then Mid(PhNum, X) = " "
  Next
  Digits = Replace(PhNum, " ", "")
End Function
 

hartsie

Board Regular
Joined
May 6, 2020
Messages
77
Office Version
  1. 2016
@VBE313

I am using Office '16. Do you know if TEXTJOIN is available in that version?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,977
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Do you know if TEXTJOIN is available in that version?
Fraid not, it's only in 2019 & 365
 

hartsie

Board Regular
Joined
May 6, 2020
Messages
77
Office Version
  1. 2016
@Rick Rothstein

Thank you very much! I will be using this. I am so pleased with this solution!

I am so grateful for your help!!!
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,981
Office Version
  1. 2016
Platform
  1. Windows
For a formula solution where the TEXTJOIN function is not available...

Posted previously by Lars-Åke Aspelin (in another forum)...
Excel Formula:
=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&A1,ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),2,300)

This is an array formula and has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER.

It has the following (known) limitations:

- The input string in cell A1 must be shorter than 300 characters

- There must be at most 14 digits in the input string. (Following digits will be shown as zeroes.)

Maybe of no practical use, but it will also handle the following two cases correctly:

- a "0" as the first digit in the input will be shown correctly in the output

- an input without any digits at all will give the empty string as output (rather than 0).
 

Watch MrExcel Video

Forum statistics

Threads
1,130,364
Messages
5,641,712
Members
417,230
Latest member
emmah44

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