# Use REGEX Function to clean phone numbers

#### hartsie

##### Board Regular
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
VBA Code:
``=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))``

#### hartsie

##### Board Regular
@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

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
@VBE313

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

#### Fluff

##### MrExcel MVP, Moderator

ADVERTISEMENT

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

#### hartsie

##### Board Regular
@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
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).

Replies
11
Views
265
Replies
11
Views
388
Replies
2
Views
254
Replies
1
Views
430
Replies
1
Views
222

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

### 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