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.