Extract Numbers from Mixed Text Strings in ExcelLearn How To Extract Number From A Mixed Text String In Excel

Written By:
Patrick Louie
Reviewed By:
FundsNet Staff

Sometimes, you might work with a dataset that contains mixed values.

What I mean by that is some cells might contain a combination of text and numbers in them.

For example, a cell may contain the value “ae71627op”.

And when a cell contains text, Excel will treat it as text (even if it has numbers in it).

This is fine if you intend to work with the dataset as is. But what if you have to work with numbers only?

Then you’d want to extract the numbers from the mixed text strings.

Believe it or not, extracting numbers from a dataset that contains cells with mixed values/text is a common data-cleaning task.

But while it is commonly done, even the latest versions of Excel don’t have a dedicated menu or function to accomplish said task.

Which is a bummer.

Thankfully, you don’t have to manually do it cell by cell.

There are workarounds to this limitation.

We can use a combination of functions to extract numbers from a mixed text string.

And in this article, we will be learning how to do just that.

By the end of this article, you should be able to reliably and efficiently extract numbers from any mixed text string.

Let’s get started!

Extract Numbers From A Mixed Text String in Excel (For Excel 2016 & Up)

In the newer versions of Excel, a new function has been added which is the TEXTJOIN function.

This, in combination with other functions, can be used to extract numbers from anywhere within the mixed text string.

Do not that this function will combine all the numeric characters together to show one number value.

For example, if the text string is “I ate 12 donuts and 10 cookies”, the number that will come out from the array formula is “1210”, not “12 10”.

We will be using a combination of the following functions to extract numbers from mixed text strings:

  • TEXTJOIN
  • IFERROR
  • MID
  • ROW
  • INDIRECT
  • LEN

Let’s see how these functions work together.

Extracting Numbers From A Mixed Text String

Suppose we have the following dataset:

What we want to do here is to extract the numbers from the mixed text string. And to do that, we will be using the following formula:

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

Where:

Cell – refers to the cell that contains the mixed text string from which we’ll be extracting numbers

  • Select a blank cell where you want to show the result of the formula. Ideally, it should be adjacent to the cell from which numbers will be extracted (we’ll select cell B2 for now).
  • Copy-paste the formula above into the selected cell. Be sure to replace CELL with the cell from which numbers will be extracted.

  • Since this is an array formula, simply pressing the ENTER won’t do. You will have to press the Ctrl + Shift + Enter keys together. The formula will then return a value that only contains the numbers from the reference cell.

  • Copy the formula to the rest of the cells (you can use the fill handle or the copy-paste function to do this).

Pretty simple, right? I suggest that you copy the formula and paste it into a Notepad .txt file so you have easy access to it.

How Does the Formula Work?

Let’s start from the very inner function:

LEN – this function returns the length of the string represented by a numerical value. It counts the number of characters in the string. For example, if you use the LEN function on “asfeas79874”, you will get “11”.

INDIRECT – this function returns a reference to a range of values

ROW – this function returns a series of numbers of a reference. The formula so far is =ROW(INDIRECT(“1:”&LEN(CELL))). This formula will return a series of numbers with the length of the string as a reference

MID – this function extracts characters within a string. Its role in the formula is to return a value of #VALUE! errors for text characters, leaving the numerical characters to stay as-is.

IFERROR – this function returns an alternative result or formula for errors in a formula. Its role in the formula is to replace #VALUE! errors with a blank.

TEXTJOIN – this function combines text from multiple ranges or strings. Its role in the formula is to combine the remaining characters in the string (which are the numbers).

Extract Numbers From A Mixed Text String in Excel (For Excel 2007, 2010, & 2013)

Unfortunately, the TEXTJOIN function is unavailable in the older versions of Excel. Still, there’s a workaround for this.

The formula will be noticeably longer though (and more complicated!).

We will be using a combination of the following functions to extract numbers from mixed text strings:

  • IF
  • SUM
  • LEN
  • SUBSTITUTE
  • SUMPRODUCT
  • MID
  • LARGE
  • INDEX
  • ISNUMBER
  • ROW

That’s a lot of functions! But wait. You haven’t seen the formula yet:

=IF(SUM(LEN(CELL)-LEN(SUBSTITUTE(CELL, {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9”}, “”)))>0, SUMPRODUCT(MID(0&CELL, LARGE(INDEX(ISNUMBER(–MID(CELL,ROW(INDIRECT(“$1:$”&LEN(CELL))),1))* ROW(INDIRECT(“$1:$”&LEN(CELL))),0), ROW(INDIRECT(“$1:$”&LEN(CELL))))+1,1) * 10^ROW(INDIRECT(“$1:$”&LEN(CELL)))/10),””)

Where:

Cell – refers to the cell that contains the mixed text string from which we’ll be extracting numbers

As you can see, the formula is long and complicated. I suggest that you save it in a Notepad .txt file so you have ready access to it.

Extracting Numbers From A Mixed Text String

We’ll be using the same dataset:

  • Select a blank cell where you want to show the result of the formula. Ideally, it should be adjacent to the cell from which numbers will be extracted (we’ll select cell B2 for now).
  • Copy-paste the formula above into the selected cell. Be sure to replace CELL with the cell from which numbers will be extracted.

  • Although this is an array formula, you can simply press the ENTER key after entering the formula (you still press Ctrl + Shift + Enter keys if you want to). The formula will then return a value that only contains the numbers from the reference cell.

  • Copy the formula to the rest of the cells (you can use the fill handle or the copy-paste functions to do this).

Use VBA to Extract Numbers from a Mixed Text String in Excel

If you’re comfortable with using a VBScript, you might want to try this method. The benefit of using this method is it works for a lot of versions of Excel (even the older ones).

The code that we’ll be using is as follows:

Function ExtractNumbers(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

ExtractNumbers = Result

End Function

Copy this code.

  • Open the Developer tab. Then click the Visual Basic button. This will open the VBA window.

  • Click on Insert then select Module.

  • In the Module window, paste the code that you copied above.

  • The code should now be ready to run

Extracting Numbers From A Mixed Text String

We’ll be using the same dataset:

  • Select a blank cell where you want to show the result of the formula. Ideally, it should be adjacent to the cell from which numbers will be extracted (we’ll select cell B2 for now).
  • Enter the formula =ExtractNumbers(CELL). This will run the code that you just added via the VBA window. Make sure to change CELL with the cell that you’ll be extracting numbers from.

  • Press the Enter The formula will then return a value that only contains the numbers from the reference cell.

  • Copy the formula to the rest of the cells (you can use the fill handle or the copy-paste functions to do this).

To have this code readily available for you, you can save the workbook you’re working on as a .xls or .xlsm file. You can also save the code in your personal macro workbook if you have one.

Conclusion

And there we have it. While there is no dedicated function for extracting numbers from a mixed text string in Excel, you can still use the methods above to do so.

You can even add it as a function using a VBA code.

Again, if you’re planning to use these methods for multiple workbooks, I suggest that you save the formulae somewhere on your computer so that you have ready access to them.