Skip to content

How to Extract Numbers from Text in Excel | 3 Methods – Power Query, VBA & TEXTJOIN

     

    In this video tutorial I look at how to extract numeric and/or text characters from a text string in Microsoft Excel. The video looks at three different ways this can be achieved.

    Click here to download the featured file.

    Method 1: Using Power Query

    Click here to view this part of the video.

    If your version of Excel does not include Power Query, use this link to download the add-in. https://www.microsoft.com/en-gb/download/details.aspx?id=39379

    To use Power Query to extract numeric or text characters, follow these steps:

    1. Select any cell containing your data
    2. Click the Data tab on Excel’s ribbon
    3. In the Get & Transform Data group, click the From Table/Range button
    4. In the Create Table dialog, click OK
    5. The Power Query Editor (PQE) will open in a separate window
    6. Select the Add Column tab on the PQE ribbon
    7. In the General group, click the Column From Examples button
    8. In Column 1 which will appear to the right of your data, type in the numeric or text characters you wish to extract from the first text string (this provides an example of what you want to extract), press ENTER on your keyboard to confirm
    9. If necessary repeat step 8 for the next text string (next row) you want to extract characters from (this provides a second example), press ENTER on your keyboard to confirm
    10. If steps 8 and 9 are sufficient for Power Query to recognise the pattern of characters you want to extract, it will repeat the extraction for the other rows in your data
    11. Click on OK to confirm (top right)
    12. You could repeat steps 7 – 11 to create another column that extracts different numeric or text characters (whatever you didn’t do with the first column)
    13. On the Home tab, click the Close & Load drop-down button and select Close and Load To
    14. In the Import Data dialog select Existing worksheet and then select the cell location for your import (in the video I select cell D1)
    15. Click OK to confirm and import the extracted data

    If you change the original data, you can easily update the query by right-clicking in any cell in your imported data and selecting Refresh.

    Method 2: VBA Function

    Click here to view this part of the video.

    Function ExtractNumbers(Value As String)
Dim StrLength As Integer
StrLength = Len(Value)
Dim i As Integer
Dim NumericChars As String

For i = 1 To StrLength
    If IsNumeric(Mid(Value, i, 1)) Then NumericChars = NumericChars & Mid(Value, i, 1)      
Next i

ExtractNumbers = NumericChars
End Function

Function ExtractText(Value As String)
Dim StrLength As Integer
StrLength = Len(Value)
Dim i As Integer
Dim TextChars As String

For i = 1 To StrLength
    If Not IsNumeric(Mid(Value, i, 1)) Then TextChars = TextChars & Mid(Value, i, 1)       
Next i

ExtractText = TextChars
End Function

    To use the VBA function method of extracting text or numeric characters from a text string, follow these steps:

    1. Open the Visual Basic Editor (VBE) using the keyboard shortcut ALF F11
    2. Create a new module, by opening the Insert menu and selecting Module
    3. You can then copy and paste the code above into the code window. The code contains two functions called ExtractNumbers and ExtractText
    4. Close down the VBE
    5. Use the VBA functions like you would use the built-in functions in Excel.  Type = and then the name of the function (ExtractNumbers or ExtractText).  The function names will appear in Excel’s IntelliSense list of function names.  Within the function brackets you need to reference the cell that you want to extract characters from
    6. You can then copy the formula down to extract characters from other cells

    Remember to save your workbook as macro-enabled otherwise your workbook will not be able to store the VBA functions.

    Method 3: Using the TEXTJOIN Function

    Click here to use this part of the video.

    You will need to watch the video using the link above to understand how these formulas work.

    First of all check that your version of Excel has the TEXTJOIN function.  Not all versions do.

    To extract numeric characters, use this formula:

    =TEXTJOIN(“”,TRUE,IFERROR(–MID(B2,ROW(INDIRECT(“1:”&LEN(B2))),1),””))

    To extract text characters, use this formula:

    =TEXTJOIN(“”,TRUE,IF(ISERROR(–MID(B2,ROW(INDIRECT(“1:”&LEN(B2))),1)),MID(B2,ROW(INDIRECT(“1:”&LEN(B2))),1),””))