While data entry, there could be instances where you’ve received data intended to be in separate columns as one string value. In larger spreadsheets, the process of manually extracting text or numbers from these string values will undoubtedly be time-consuming.
Well, what if we told you there’s a way to automate this tedious process? We would technically be lying because we’ve gathered not just one but four ways to extract numbers from a string value in Excel. More, the merrier!
Flash Fill
If you’re looking to extract numbers from a range of strings that follow a sequence, you can use Flash Fill. Flash Fill is an amazing feature offered by Excel that automatically fills your cells with data when it notices a pattern. For this method, you must convert your range to a table. Additionally, there must be a delimiter separating your text and number value.
In this example, we’re extracting numbers from five-string values. First, we converted range A1:B6 to a table. Cell range A2:A6 holds our five string values and we’re about to extract our numbers in range B2:B6.
We entered our first number from the string to establish a pattern. Usually, when you enter the next number, Excel immediately notices the pattern and spills the remaining data. To confirm the change, hit the Tab key.
If in case this doesn’t happen, keep the cell selected then, head to the Data tab. From the Data Tools section, select Flash Fill.
Using Formula
Excel has plenty of functions that could help you in extracting numbers from a string. For this method, we will be using a combination of the new TEXTJOIN function with LEN, INDIRECT, BMID, ROW, VALUE, and the IFERROR functions.
Here’s a little breakdown of these functions and what they actually do
| Function | Format | Description |
| TEXTJOIN | =TEXTJOIN(delimiter, ignore_empty, text1, ..) | You need to specify a delimiter using which the function will merge split values. You must also pass a boolean value in the ignore_empty section to prompt the function whether you want to include empty cells or not. |
| LEN | =LEN(text) | Returns the number of characters used in the function. For example, =LEN(“Friday 2345”) returns 11. |
| INDIRECT | =INDIRECT(ref_text, [a1]) | You must specify a reference text as the first argument. In the second section, pass TRUE if you want to use the A1 reference style and FALSE if you want to use the R1C1 reference type. |
| MID | =MID(text, start_num, num_chars) | The MID function returns a character from the position you specify in the second argument. |
| ROW | =ROW() | Returns the position of the active cell in the grid. |
| VALUE | =VALUE(text) | Converts a number formatted as text into the number format. |
| IFERROW | =IFERROR(value, value_if_error) | The IFERROR is a conditional function that returns a set value if the referred cell has an error. |
Now that we know each function and its application, let’s put it to use. In this example, we’re looking to extract numbers from a string value in cell A2. In cell C2, we entered this formula:
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))
As this is an Array, please use Ctrl + Shift + Enter instead of Enter when you’re done entering the formula. If you simply use Enter, the formula will not loop, giving you only the first number out of the string.
Here’s how the formula works:
LEN(A2): We first extracted the number of characters in “Total saving amount is 7653” which is in cell A2. The value returned is 27.ROW(INDIRECT(“1:”&LEN(A2))): This formula returns numbers from 1 to the number of characters in the referred cell. In our formula, as the length of our cell is 27, this formula returns 1,2,3,4,5,…,27.MID(A2,ROW(INDIRECT(“1:”&LEN(A2)))): The MID formula extracts value from cell A2 from the position specified in the next argument. In this situation, the MID formula loops 27 times to extract each value from cell A2. The value is then multiplied by 1.IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""): Once the value is multiplied by 1. If the value is a text, it returns the #VALUE! error. The IFERROR checks this instance and passes TRUE or FALSE depending on if you receive a #VALUE! Error.TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),"")): If the condition is TRUE, the number is extracted. A blank value is passed if the boolean value is FALSE. The TEXTJOIN function then combines all non-empty values, thus extracting your string value.
Power Query
The Power Query splits digits from strings instantly. Power Query is a powerful utility in Excel that mostly deals with automation. You can use Power Query if your string looks something like “Frank938736”. If the numbers in your string value are dispersed like “f67ve863h”, this will not be the best method for you.
- Select your range.
- Head to Data > From Table.
- If you’re prompted to convert your range to a table, select Ok.
- In the Power Query editor, right-click on the column with your string.
- Select Split Column > Non-Digit to Digit.
- From the Home tab, select Close and Load.
If in case you do not want to separate the original value, you can duplicate the original range and follow these steps. You can then delete the column with the text value and load only the column with the numbers.
Visual Basic Editor
We might’ve already figured that there isn’t a dedicated function to extract numbers from a string value. Well in that case, why not create our very own function? Excel is not limited to its more than 450 functions. We can create our very own function using the Visual Basic Editor.
First and foremost, head to the Developer tab. If you cannot locate the Developer tab, it must be hidden.
Step 1: Enable Developer Tab
- Head to File > Options.
- From the sidebar, choose Custom Ribbon.
- Under Main Tabs, select the box next to Developer.
- Click Ok.
Step 2: Launch VBA
- Head to the Developer tab.
- Click Visual Basic.
- Under Microsft Excel Objects, locate the sheet you want to load the function and right-click on it.
- Select Insert > Module.
Step 3: Enter Code
Enter the following code on the left.
Function ExtractNumbers(CellValue As String) As String
Dim Length As Integer
Length = Len(CellValue)
For Count = 1 To Length
If IsNumeric(Mid(CellValue, Count, 1)) Then Number = Number & Mid(CellValue, Count, 1)
Next Count
ExtractNumbers = Number
End Function
Close the VBA editor screen and use the ExtractNumbers function like you would use any other function. Then as an argument, pass the cell you wish to extract numbers from.