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 (+ - ! ( ) )?
 
@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. ?
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
:D HA!

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

Thank you, again.
 
Upvote 0
As I'm currently unemployed, definitely underpaid. ?
 
Upvote 0
@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.
 
Upvote 0
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
 
Upvote 0
Since this thread was briefly revived - until some posts were removed, here is an answer to your original question
Does anyone know of a VBA Function using REGEX that can pull out the phone number digits and ignore special characters and spaces?

VBA Code:
Function JustDigits(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\D"
    JustDigits = .Replace(s, "")
  End With
End Function

hartsie.xlsm
AB
1+(321)155-63783211556378
223659895412365989541
3+1234*7[65]4#231234765423
4(04)285698950428569895
Sheet1
Cell Formulas
RangeFormula
B1:B4B1=JustDigits(A1)
 
Upvote 0
Since this thread was briefly revived - until some posts were removed, here is an answer to your original question


VBA Code:
Function JustDigits(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\D"
    JustDigits = .Replace(s, "")
  End With
End Function

hartsie.xlsm
AB
1+(321)155-63783211556378
223659895412365989541
3+1234*7[65]4#231234765423
4(04)285698950428569895
Sheet1
Cell Formulas
RangeFormula
B1:B4B1=JustDigits(A1)
[/RAN
Cell Formulas
RangeFormula
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
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