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

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

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

- CONCATENATE Excel Ranges (with and without separator).
- A Beginner’s Guide to Using For Next Loop in Excel VBA.
- Using Text to Columns in Excel.
- How to Extract a Substring in Excel (Using TEXT Formulas).
- 20 Useful Excel Macro Examples for VBA Beginners.