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):

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.

This Tutorial Covers:

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),""))`

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.

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),""))`

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.
• Click on Visual Basic (You can also use the keyboard shortcut ALT + F11)
• In the VB Editor backend that opens, right-click on any of the workbook objects.
• Go to Insert and click on Module. This will insert the module object for the workbook.
• In the Module code window, copy and paste the VBA code mentioned above.
• 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.

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

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.
• Click on Visual Basic (You can also use the keyboard shortcut ALT + F11)
• In the VB Editor backend that opens, right-click on any of the workbook objects.
• 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).
• In the Module code window, copy and paste the VBA code mentioned above.
• 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.

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:

• William Nineteen says:

Hi Summit,

You might want to also consider the decimal symbol (period/dot “.” for North America, or comma “,” depending on the locale/country)

In most cases of a get numeric, you’ll want the decimal
I was processing US\$1,425K and I would get 1425 (the comma isn’t relevant)
But when I had US\$250.5K, I would get 12505. In this case, the desired number is 250.5 (the decimal is relevant)

So, I added Or Mid(CellRef, i, 1)=”.” for my purposes

Note that other countries would have US\$1.425,5K

• DIOCANE says:

DONT WORK FAGGOT

• Khalid Alzahrani says:

Dear, These cells have codes (? *) and (?) and (“Low Vol”) and numbers with code (67,306,444 *) how can I separate the numbers and use them for calculating. Unfortunately, function Getnumber not helping especially when all codes in the same column with more than 2 thousand cells.

Here the challenging

• rick boen says:

I’m using Excel 2016. The textjoin function is only available in 365. I put in the VBA formulas to no avail. Not only that, but now no functions or calculations will work on the spreadsheet. Please help.

• shishir sharma says:

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.

• Vikas G says:

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

• Franz says:

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 says:

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