Extract 10-Digit and 7-Digit Phone Numbers in Excel Using VBA
Question details
The user needs to extract both 10-digit and 7-digit phone numbers from mixed text strings in a single column and output the results into separate columns.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning up inconsistent text data to isolate and extract valid phone numbers of different lengths using custom functions.
- Observed behavior
- Text strings contain mixed data, requiring pattern matching (Regular Expressions) via VBA to reliably extract 10-digit and 7-digit numbers without manual data entry.
Ensure your Excel workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled Developer options to access the VBA editor.
Create a VBA Custom Function Using Regular Expressions
Use a User-Defined Function (UDF) powered by VBScript.RegExp to identify and extract 7-digit phone numbers from text strings.
Regular expressions (RegEx) are highly effective at finding specific text patterns, such as phone numbers, hidden within chaotic data strings. By building a custom function, you can use RegEx seamlessly as a standard formula in your Excel sheets.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor. Alternatively, navigate to the Developer tab and click on 'Visual Basic'.
In the VBA Editor, click 'Insert' from the top menu and select 'Module'. This will create a blank script window where you can paste your custom function.
Copy and paste the following code into the module window: Public Function GetPhoneNumberSevenD(inputString As String) As String Dim regex As Object Dim matches As Object GetPhoneNumberSevenD = "" Set regex = CreateObject("VBScript.RegExp") regex.Pattern = "([0-9]{3})[-. ]?([0-9]{4})" regex.Global = True Set matches = regex.Execute(inputString) If matches.Count = 0 Then Exit Function GetPhoneNumberSevenD = regex.Replace(matches(0).Value, "$1$2") End Function
Save your workbook (ensuring it is an .xlsm file format) and close the VBA Editor to return to your standard Excel worksheet.
Use Conditional Formulas to Organize the Extracted Numbers
Combine the custom 10-digit and 7-digit VBA functions with an IF statement to neatly place results in separate columns, avoiding duplicates.
Use Power Query for Complex Data Cleaning
If the data is highly inconsistent (e.g., contains extensions, embedded letters, or multiple numbers), use Power Query to transform and extract data in controlled steps.
Extract Phone Numbers Using WPS Spreadsheet
WPS Spreadsheet offers full support for VBA macros and Regular Expressions, allowing you to seamlessly create custom user-defined functions to extract phone numbers from messy text data. You can perform complex data cleaning efficiently without needing advanced programming environments.
- 1. Open WPS Spreadsheet: Launch WPS Office, create or open your existing spreadsheet containing the raw text data.
- 2. Access the VBA Editor: Navigate to the 'Tools' tab on the ribbon and click on 'Macros', then select 'Visual Basic Editor'.
- 3. Insert and Run the Code: Go to Insert > Module, paste the RegExp phone number extraction code, and save. You can now use the custom formula directly in your worksheet cells.
Frequently Asked Questions
Why does my VBA RegEx function return a #NAME? error?
A #NAME? error occurs if Excel doesn't recognize the function. Ensure that your workbook has macros enabled, and that you have pasted the VBA code into a standard 'Module' rather than a worksheet object or 'ThisWorkbook'.
How do I handle phone numbers that contain extensions?
To handle extensions (e.g., 'x123' or 'ext 456'), you need to adjust the Regular Expression pattern in your VBA code to account for trailing letters and numbers, or use Excel's 'Text to Columns' tool to split the extensions into a separate column before running the extraction function.
Can I extract multiple phone numbers from a single cell?
Yes, but the provided VBA code uses 'matches(0)' which only extracts the first match found. To extract multiple numbers, you will need to modify the VBA code to loop through all objects in the 'matches' collection and concatenate them or output them as an array.
Are 10-digit phone numbers extracted using the same method?
Yes. A 10-digit extraction function uses the exact same VBScript.RegExp method but requires a slightly longer pattern, such as "([0-9]{3})[-. ]?([0-9]{3})[-. ]?([0-9]{4})", to account for the area code.
