Get Only the Numeric or the Text Part from a String Using Excel Formulas

There is no inbuilt function in Excel to remove all the text part from a string and get only the numeric part (or vice versa –  remove the numeric part and give only the text part from a string in a cell).

However, this can be done using a cocktail of Excel functions.

Let me show you exactly what I am talking about.

Suppose you have a data set as shown below and you want to separate the numeric and text part from it (as shown below):

Dataset to get the numeric or the text part in Excel

Note that column B only has the text part from the string and column C only has the numeric part.

In this tutorial, I will show you how to extract the text and numeric part from a string using:

  • Inbuilt Excel functions
  • Using a Custom formula created using VBA

Until Excel 2016, it was extremely difficult to do this using Excel in-built functions.

However, in Excel 2016, a new function was introduced – the TEXTJOIN function. And this function made is possible to get the numeric/text part from a string in Excel.

Click here download the example file

Getting the Numeric Part Using Excel Formulas

Below is the formula that will give you numeric part from a string in Excel.

=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))

Getting the Numeric Part from String using Excel Formula

This is an array formula, so you need to use ‘Control + Shift + Enter’ instead of using Enter.

How does this formula work?

Let me break this formula and try and explain how it works:

  • ROW(INDIRECT(“1:”&LEN(A2))) – this part of the formula would give a series of numbers starting from one. The LEN function in the formula returns the total number of characters in the string. In case of “The cost is USD 100”, it will return 19. The formulas would thus become ROW(INDIRECT(“1:19”). The ROW function will then return a series of numbers – {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
  • (MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1) – This part of the formula would return an array of #VALUE! errors or numbers based on the string. All the text characters in the string become #VALUE! errors and all numerical values stay as is. This happens as we have multiplied the MID function with 1.
  • IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””) – When IFERROR function is used, it would remove all the #VALUE! errors and only the numbers would remain. The output of this part would look like this – {“”;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;1;0;0}
  • =TEXTJOIN(“”,TRUE,IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””)) – The TEXTJOIN function now simply combines the string characters that remains (which are the numbers only) and ignores the empty string.

Note that this formula would give you all the numeric characters together. For example, if the text is “The price of 10 tickets is USD 200”, it will give you 10200 as the result.

Pro Tip: If you want to check the output of a part of the formula, select the cell, press F2 to get into the edit mode, select the part of the formula for which you want the output and press F9. You will instantly see the result. And then remember to either press Control + Z or hit the Escape key. DO NOT hit the enter key.

Download the Example File

Getting the Text Part Using Excel Formulas

Below is the formula that would get the text part from the string:

=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),""))

Getting the Text Part from String using Excel Formula

This is an array formula, so you need to use ‘Control + Shift + Enter’ instead of using Enter.

Again, this formula also follows a similar logic explained above.

A minor change in this formula is that IF function is used to check if the array we get from MID function are errors or not. If it’s an error, it keeps the value else it replaces it with a blank.

Then TEXTJOIN is used to combine all the text characters.

Getting the Numeric Part Using VBA

If you’re using Excel 2013 or prior versions of Excel, you will not be able to use the TEXTJOIN function.

In that case, you can create a custom function using VBA.

Once created, you can use this custom function (just like any other Excel function) to strip the numeric or text characters from a string.

In this part, I will show you how to create the custom function to get only the numeric part from a string.

Below is the VBA code we will use to create this custom function:

Function GetNumeric(CellRef As String)
Dim StringLength As Integer
StringLength = Len(CellRef)
For i = 1 To StringLength
If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)
Next i
GetNumeric = Result
End Function

Here are the steps to create this function and then use it in the worksheet:

  • Go to the Developer tab.Developer Tab in Excel Ribbon
  • Click on Visual Basic (You can also use the keyboard shortcut ALT + F11)Visual Basic in the Ribbon in Excel - To create custom function
  • In the VB Editor backend that opens, right-click on any of the workbook objects.Right click on VBA Backend
  • Go to Insert and click on Module. This will insert the module object for the workbook.Insert a module in VBA Back end
  • In the Module code window, copy and paste the VBA code mentioned above.Custom function to get the numeric part from the text
  • Close the VB Editor.

Now, you will be able to use the GetText function in the worksheet. Since we have done all the heavy lifting in the code itself, all you need to do is use the formula =GetNumeric(A2). 

This will instantly give you only the numeric part of the string.

Using Custom VBA Function to get only the numeric part from a string in Excel

Note that since the workbook now has VBA code in it, you need to save it with .xls or .xlsm extension.

Download the Example File

Getting the Text Part Using VBA

In this part, I will show you how to create the custom function to get only the text part from a string.

Below is the VBA code we will use to create this custom function:

Function GetText(CellRef As String)
Dim StringLength As Integer
StringLength = Len(CellRef)
For i = 1 To StringLength
If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1)
Next i
GetText = Result
End Function

Here are the steps to create this function and then use it in the worksheet:

  • Go to the Developer tab.Developer Tab in Excel Ribbon
  • Click on Visual Basic (You can also use the keyboard shortcut ALT + F11)Visual Basic in the Ribbon in Excel - To create custom function
  • In the VB Editor backend that opens, right-click on any of the workbook objects.Right click on VBA Backend
  • Go to Insert and click on Module. This will insert the module object for the workbook.
    • If you already have a module, double-click on it (no need to insert a new one if you already have it).Insert a module in VBA Back end
  • In the Module code window, copy and paste the VBA code mentioned above.Custom function to get the Text part from the string
  • Close the VB Editor.

Now, you will be able to use the GetNumeric function in the worksheet. Since we have done all the heavy lifting in the code itself, all you need to do is use the formula =GetText(A2).

This will instantly give you only the numeric part of the string.

Using Custom VBA Function to get only the Text part from a string in Excel

Note that since the workbook now has VBA code in it, you need to save it with .xls or .xlsm extension.

You May Also Like the Following Excel Tutorials:

  • Franz

    If the numeric part is always at the end, like in your examples, I think is more efficient the following formula to extract the numeric part: =VALUE(TRIM(MID(A2,FIND(“|”,SUBSTITUTE(A2,” “,”|”,LEN(A2)-LEN(SUBSTITUTE(A2,” “,””)))),9999999))) and the following one to extract just the text =TRIM(LEFT(A2,FIND(“|”,SUBSTITUTE(A2,” “,”|”,LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))))))

    • Thanks for sharing Franz.. This formula doesn’t work if there are no spaces. For example, if the text is “ABC123”, then it would return an error.

      • Mohammed Masihuddin

        Hey @Sumit, need your help on creating an Attendance tracker using formulas in Excel.

  • Vikas G

    UDF with a modification:-
    ———————————————————————-
    Function GetNumeric(CellRef As String)
    Dim StringLength As Integer
    Dim Result ””””””””””””””””””””’
    Result = “”
    StringLength = Len(CellRef)
    For i = 1 To StringLength
    If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)
    Next i
    GetNumeric = Result * 1 ””””””””””””””””””””
    End Function
    —————————————————————————-
    Function GetText(CellRef As String)
    Dim StringLength As Integer
    StringLength = Len(CellRef)
    Dim Result As String ””””””””””””””””””””
    Result = “”
    For i = 1 To StringLength
    If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1)
    Next i
    GetText = Result
    End Function

  • shishir sharma

    Hi Sumit i watch all the published video of yours and find it interesting and useful….i need your small help today i wanted to convert numeric value in words without using macro if there is any formula for the same please help.