Use REGEX Function to clean phone numbers

hartsie

Board Regular
Joined
May 6, 2020
Messages
84
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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
VBA Code:
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))

Snag_e867b27.png
 
Upvote 0
@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.
 
Upvote 0
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
 
Upvote 0
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).
 
Upvote 0
Solution

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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
Back
Top