How to convert the phone number format to digits in Excel?

When working with Excel worksheets that contain lists of phone numbers, you may often encounter various formatting styles, such as parentheses, spaces, or dashes. These non-digit characters can interfere with data analysis, system imports, or automated dialing processes that require phone numbers to be in a pure digit sequence. If you need to standardize your data by extracting only the digits from any formatted phone number, this article provides several practical solutions to achieve this efficiently in Excel.

Here’s an example of the situation you might face—the left column contains phone numbers in different formats, while the right column shows the desired result: phone numbers with all non-digit characters removed, leaving only a continuous string of numerals.

Convert the phone number format to digits with formula
Use Flash Fill to extract digits from formatted phone numbers
Convert the phone number format to digits with Kutools for Excel
Use a macro to strip all non-digit characters from phone numbers

Convert the phone number format to digits with formula

Excel’s formulas allow you to remove unwanted characters and keep only digits in your phone number list. This method is especially useful if you prefer not to use add-ins or programming, and works well with phone numbers that follow a consistent format, such as (123)456-7890 or123-456-7890. However, if your data contains additional non-standard symbols or letters, more advanced techniques may be needed.

1. Select a blank cell (for example, C2) where you wish to display the extracted digits. Enter the following formula and press Enter:

=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","")," ",""),"-","")

Note: In this formula, A2 is the reference to the cell containing the original phone number. You can modify this reference if your data is in a different column or row.

2. While still selecting cell C2, drag the fill handle (the small square at the cell’s bottom-right corner) down to fill the formula for the rest of your list (for example, from C2 to C8). This will apply the formula to each phone number in the range.

Now, each phone number in your data range should be converted to a sequence of digits only.

Use Flash Fill to extract digits from formatted phone numbers

Excel’s Flash Fill feature offers a smart, manual alternative for extracting digits from phone numbers—especially effective when dealing with consistent list formats or when you want a fast, formula-free approach. Flash Fill evaluates patterns based on your manual entry, then fills in the rest of the column to match your example.

1. Assume your phone numbers are in column A, starting from cell A2. In the adjacent column (B2), manually type only the digits from your first phone number, omitting any symbols or spaces (for example: 1234567890).

2. Move to cell B3. Begin typing the digits from the next phone number. As you start, Excel will automatically detect the pattern and display a suggested autofill (highlighted in gray below the typed value).

3. Press Enter to accept the Flash Fill suggestion, and Excel will fill down the column with the extracted digits for all corresponding rows.

Convert the phone number format to digits with Kutools for Excel

For users looking for a more convenient, interactive solution, the Remove Characters utility in Kutools for Excel can efficiently remove specified symbols—including dashes, spaces, brackets, and more—from selected cells. This approach is ideal for those who frequently process structured data and want a quick, menu-driven method to clean up phone numbers without writing formulas or code.

1. Select the range containing the phone numbers you want to convert to digits, then navigate to Kutools > Text > Remove Characters.

2. In the Remove Characters dialog box, select the Custom option, and enter a hyphen – into the text box. You can also specify other characters you want to remove, such as spaces, parentheses, or periods, by adding them into the box. After setting, click OK to process the range.

The selected phone numbers will be updated instantly, with the specified characters removed, leaving a clean sequence of digits as shown below.

Use a macro to strip all non-digit characters from phone numbers

If your task involves numerous phone numbers in mixed or unpredictable formats (including special characters, spaces, extra punctuation, text, or even country codes), Excel’s VBA macros can provide a highly flexible and automatic way to clean these entries—all at once. This approach is especially useful for large datasets or repetitive cleaning tasks.

Applicable scenario: Use VBA if your phone number formats are irregular, you have many rows to process, or if neither formulas nor built-in features fully solve your needs. However, using macros requires saving your workbook in a macro-enabled format (*.xlsm) and granting macro permissions.

Steps:

1. Press Alt + F11 to open the Visual Basic for Applications editor. In the new window, click Insert > Module to create a blank module, then copy and paste the following VBA code into the code window:

Sub StripNonDigitsFromPhoneNumbers()
    Dim xRg As Range
    Dim xCell As Range
    Dim xDigits As String
    Dim i As Integer
    Dim xTitleId As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set xRg = Application.Selection
    Set xRg = Application.InputBox("Select the range with phone numbers", xTitleId, xRg.Address, Type:=8)
    
    For Each xCell In xRg
        xDigits = ""
        For i = 1 To Len(xCell.Value)
            If Mid(xCell.Value, i, 1) Like "#" Then
                xDigits = xDigits & Mid(xCell.Value, i, 1)
            End If
        Next i
        xCell.Value = xDigits
    Next xCell
End Sub

2. And click the Run button (or press F5). In the dialog that appears, confirm or select your data range. The macro will automatically strip out all non-digit characters from each cell in your selection—leaving only the pure number sequence.

Related articles:

• How to format phone number with extension in Excel?