Although Microsoft Excel has not provided a straight formula or syntax to extract only numbers from the cells, we can incorporate a wide range of **Excel formulas** to make a single function to use for extracting numbers or digits only from Excel cells. In this article, Iâ€™ll try to show & explain in detail how we can bring out only numbers from the cells with the appropriate formulas under a few criteria.

**Table of Contents**hide

**Download Practice Workbook**

Download the practice book for free that weâ€™ve used to prepare this article. You can input the text values with numbers in the selected cells & find the results immediately through embedded formulas.

**6 Effective Ways to Extract Only Number from Excel Cell**

**1. ****Pulling Out Numbers from the Beginning of a Text through the Mixture of LEFT, SUM, LEN & SUBSTITUTE Functions**

Like in the picture below, we have some codes including digits & letters where digits are present at the beginning. We have to extract those digits or numbers only. For the first one, we have to type the formula in** cell C5**â€“

`=LEFT(B5,SUM(LEN(B5)-LEN(SUBSTITUTE(B5,{"0","1","2","3","4","5","6","7","8","9"},""))))`

Now press** Enter **& youâ€™ll get the number 34 for the first code. Use **Fill Handle** then to autofill all other cells in **Column C.**

**đź”Ž Breakdown of the Formulas:**

**âž¤ SUBSTITUTE(B5,{"0","1","2","3","4","5","6","7","8","9"},"")**

Here, **SUBSTITUTE** function will find the digits(0-9) consecutively and if found then it will replace that digit in **Cell B5** with empty character every time. So, the function will return as-

{â€ś34DTXRFâ€ť,â€ť34DTXRFâ€ť,â€ť34DTXRFâ€ť,â€ť4DTXRFâ€ť,â€ť3DTXRFâ€ť,â€ť34DTXRFâ€ť,â€ť34DTXRFâ€ť,â€ť34DTXRFâ€ť,â€ť34DTXRFâ€ť,â€ť34DTXRFâ€ť}

**âž¤ LEN(SUBSTITUTE(B5,{"0","1","2","3","4","5","6","7","8","9"},""))**

**LEN** function determines the number of characters in a string. So, here, the **LEN** function will count all the characters individually found in the texts through the **SUBSTITUTE** function. The resultant values will be here in our case-

`{7,7,7,6,6,7,7,7,7,7}`

**âž¤ LEN(B5)-LEN(SUBSTITUTE(B5,{"0","1","2","3","4","5","6","7","8","9"},"")))**

Now, this part is the subtraction from the number of characters in** Cell B5 **to all other numbers of characters found individually in the previous section of the formula. So, here the resultant values will be-

`{0,0,0,1,1,0,0,0,0,0}`

**âž¤ SUM(LEN(B5)-LEN(SUBSTITUTE(B5,{"0","1","2","3","4","5","6","7","8","9"},"")))**

The** SUM** function will then simply sum all the subtracted values found & so the result will be here =** 2** (0+0+0+1+1+0+0+0+0+0)

**âž¤ =LEFT(B5,SUM(LEN(B5)-LEN(SUBSTITUTE(B5,{"0","1","2","3","4","5","6","7","8","9"},""))))**

And now hereâ€™s the final part where the **LEFT** function will return the values with an exact number of characters from the left found through the previous section of the formula. As we got the sum value as 2 so **LEFT** function here will return only **34** from the text **34DTXRF**.

**âžĄ Related: How to Separate Numbers in Excel Using Formula (5 Ways)**

**2. ****Extracting Numbers from the Right of a Text or Number String by Combining RIGHT, LEN, MIN & SEARCH Functions**

Now weâ€™ll extract the digits or numbers from the right of a text string. So, in our dataset what we have to type in** Cell C5** is-

`=RIGHT(B5,LEN(B5) - MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, B5&"0123456789")) +1)`

Press **Enter** & then use **Fill Handle** to autofill the rest of the cells.

**đź”Ž Breakdown of the Formulas:**

**âž¤ B5&â€ť0123456789â€ł**

Here, weâ€™re concatenating values in the **B5 Cell** with **0123456789 **by using **Ampersand(&) **between them and weâ€™ll get the resultant value as-

DTXRF340123456789

**âž¤ SEARCH({0,1,2,3,4,5,6,7,8,9}, B5&"0123456789")**

Now, the **SEARCH** function will search for all the digits(0-9) one by one in the resultant value got from the previous section and will return the positions of those 10 digits in the characters of **DTXRF340123456789**. So, here our resultant values will be-

`{8,9,10,6,7,13,14,15,16,17}`

**âž¤ MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, B5&"0123456789"))**

**MIN** function is used to find the lowest digit or number from an array. So, here the minimum or lowest value will be-** 6** from the array {8,9,10,6,7,13,14,15,16,17} found in the preceding section of the formula.

**âž¤ LEN(B5) - MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, B5&"0123456789")) +1)**

Now, the number of characters in** B5** will be found by the **LEN** function. Then itâ€™ll subtract the value 6(found in the last section) & then return the result by adding 1. Here in our case, the resultant value will be **2(7-6+1)**

**âž¤ RIGHT(B5,LEN(B5) - MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, B5&"0123456789")) +1)**

The **RIGHT** function will return the specified number of characters from the last or right side of a string. Following the result found through the subtraction process in the previous section, here the **RIGHT **function will show the last 2 characters from **Cell B5** and thatâ€™ll be **34**.

**Read More: How to Separate Numbers in One Cell in Excel (5 Methods)**

**3. ****Taking Out Numbers from Any Part of a Text String**

Now, hereâ€™s the ultimate solution for all cases. This method will pull out numbers or digits from any position in a text string. So type the formula in your destination cell as follows-

`=TEXTJOIN("",TRUE,IFERROR((MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)*1),""))`

If youâ€™re using **Excel 2016** or higher version then press **Enter**, otherwise press **CTRL+Shift+Enter** to get the result for this array formula. After this step, autofill other cells using **Fill Handle** & youâ€™re done.

**đź”Ž Breakdown of the Formulas:**

**âž¤ INDIRECT("1:"&LEN(B5))**

The** INDIRECT** function is used to store an array or cell values as a reference text. Here **Ampersand(&)** command concatenates the length of the characters of **Cell B5** with incomplete range syntax **(1:)**. So, here **INDIRECT** function will store all the numbers between 1 & the length of the characters in **Cell B5** as a reference text.

**âž¤ ROW(INDIRECT("1:"&LEN(B5)))**

**ROW** function usually tells the row number of a cell. But her in the **INDIRECT** function, as no reference cell has been mentioned, in this case, the **ROW** function will extract all the values or numbers from the reference texts stored in the** INDIRECT** function. Now for the 1st **Cell B5**, the resultant values through these **ROW & INDIRECT** functions will be-

`{1;2;3;4;5;6;7;8;9}`

**âž¤ (MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))**

The** MID** function will let you determine the characters from the middle of a text string, given a starting position & length. So, here for all 9 positions found in the previous section, the **MID** function now will show all the characters one by one for each position & thus will return the values as-

`{"1";"9";" ";"D";"D";"X";"2";"M";"N"}`

**âž¤ IFERROR((MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)*1),"")**

Now the **IFERROR** is a logical function that will determine if a string is a number or something else. If it does not identify a string with numbers or digits then itâ€™ll return the value with a defined text command. In our case, all the values found in the last section will be multiplied by 1 and when the results will return as value error for letters or text values that cannot be multiplied, their** IFERROR** function will convert the errors into empty strings. So, our resultant values will be then-

`{1;9;"";"";"";"";2;"";""}`

**âž¤ =TEXTJOIN(â€śâ€ť,TRUE,IFERROR((MID(B5,ROW(INDIRECT(â€ś1:â€ť&LEN(B5))),1)*1),â€ťâ€ť))**

And now the final part will be executed through the **TEXTJOIN** function. This function is used to concatenate or join two strings with a specified delimiter. So, the resultant values we have found in the preceding section will now be joined together alongside this **TEXTJOIN** function. And thus weâ€™ll get the number **192.**

**Read More: How to Extract Multiple Numbers from String in Excel (6 Methods)**

### 4. Bringing Out Only Numbers from Any Position in a Cell with the Ultimate Formula

You can also apply this great formula too, although it may seem quite more complex but Iâ€™ll break down the whole formula & try to explain all the compact functions with ease.

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

You have to replace the cell reference only based on your own cell in the spreadsheet & then by embedding this formula, youâ€™ll get the expected result right away. And this formula works perfectly in any version of Excel. You have to press **Enter** only after typing the entire formula & youâ€™re done.

**đź”Ž**** Detailed Explanation & Breakdowns of the Formulas:**

Before starting the breakdown of this massive & compact formula, we can separate it into some parts like-

**=IF(A>0, SUMPRODUCT(B _{1}*C_{1}, B_{2}*C_{2}, â€¦â€¦â€¦.B_{n}C_{n}),â€ťâ€ť)**

This syntax means if **A** is greater than 0, then all the products of **B**_{n}** & C**** _{n}** will sum up to the final result. And if

**A**is not greater than 0 then the result will return as an empty or blank cell.

**A**` = SUM(LEN(B5)-LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"}, ""`

**B**` = MID(0&B5, LARGE(INDEX(ISNUMBER(--MID(B5,ROW(INDIRECT("$1:$"&LEN(B5))),1))* ROW(INDIRECT("$1:$"&LEN(B5))),0), ROW(INDIRECT("$1:$"&LEN(B5))))+1,1)`

**C**` = 10^ROW(INDIRECT("$1:$"&LEN(B5)))/10),""`

**Breakdown of Part A = SUM(LEN(B5)-LEN(SUBSTITUTE(B5, {â€ś0â€ł,â€ť1â€ł,â€ť2â€ł,â€ť3â€ł,â€ť4â€ł,â€ť5â€ł,â€ť6â€ł,â€ť7â€ł,â€ť8â€ł,â€ť9â€ť}, â€śâ€ť**

**âž¤ SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"}, "")**

**SUBSTITUTE** function will find for all digits(0-9) one by one in the text **19 DDX2MN** each time & will replace those digits with empty string in the positions of of the digits. Thus the resultant values in an array will be-

{â€ś19 DDX2MNâ€ť,â€ť9 DDX2MNâ€ť,â€ť19 DDXMNâ€ť,â€ť19 DDX2MNâ€ť,â€ť19 DDX2MNâ€ť,â€ť19 DDX2MNâ€ť,â€ť19 DDX2MNâ€ť,â€ť19 DDX2MNâ€ť,â€ť19 DDX2MNâ€ť,â€ť1 DDX2MNâ€ť}

**âž¤ LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"}, ""))**

**LEN** function will now count the number of characters in all string values obtained from the previous section. So, this function will return as-

{9,8,8,9,9,9,9,9,9,8}

**âž¤ LEN(B5)-LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"}, ""))**

Now in this part of the formula, a number of characters in **Cell B5** will subtract all the numbers found in the preceding section. The resultant values will be then-

{0,1,1,0,0,0,0,0,0,1}

**âž¤ SUM(LEN(B5)-LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"}, "")))**

With the help of the **SUM** function, the values inside the array found in the last section will add up to 3 (0+1+1+0+0+0+0+0+0+1)

So, according to the first part of our formula, **A>0 (3>0)**. Now, weâ€™ll move to the next part of the breakdown.

**Breakdown of Part B = MID(0&B5, LARGE(INDEX(ISNUMBER(--MID(B5,ROW(INDIRECT("$1:$"&LEN(B5))),1))* ROW(INDIRECT("$1:$"&LEN(B5))),0), ROW(INDIRECT("$1:$"&LEN(B5))))+1,1)**

**âž¤ INDIRECT("$1:$"&LEN(B5))**

The** INDIRECT **function here will store the string values as a reference of the array. Inside the parenthesis, **Ampersand(&)** command will join the number of characters found from Cell B5 with the Range of Cells syntax. It means, that from 1 to the number of characters defined will be stored as an array reference.

**âž¤ ROW(INDIRECT("$1:$"&LEN(B5)))**

Now, this **ROW** function will pull out all the numbers from the array and the resultant values for **Cell B5** will be-

{1;2;3;4;5;6;7;8;9}

**âž¤ MID(B5,ROW(INDIRECT("$1:$"&LEN(B5))),1)**

In this part of the formula,** the MID** function will express all the characters from **Cell B5** based on all the positions found as numbers in the previous section. So, the extracted values will be found after this part-

{â€ś1â€ł;â€ť9â€ł;â€ť â€ś;â€ťDâ€ť;â€ťDâ€ť;â€ťXâ€ť;â€ť2â€ł;â€ťMâ€ť;â€ťNâ€ť}

**âž¤ ISNUMBER(--MID(B5,ROW(INDIRECT("$1:$"&LEN(B5))),1))**

As **ISNUMBER** is a logical function, so itâ€™ll determine individually if the values found in the preceding section are number strings or not. If yes then itâ€™ll return as **TRUE**, otherwise, itâ€™ll display as** FALSE**. So, in our case, the result will be-

{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}

**âž¤ INDEX(ISNUMBER(--MID(B5,ROW(INDIRECT("$1:$"&LEN(B5))),1))*ROW(INDIRECT("$1:$"&LEN(B5))),0)**

If you notice inside the above function, a double-hyphen, known as **Double Unary** has been used. It is used to convert all logical values into number strings- **1(TRUE)** or **0(FALSE)**. Now, the **INDEX** function will return this result as-

{1;1;0;0;0;0;1;0;0}

After that, the resultant values will be multiplied by the values obtained from the **ROW** function inside the array and the outcome will be-

{1;2;0;0;0;0;7;0;0}

**âž¤ LARGE(INDEX(ISNUMBER(--MID(B5,ROW(INDIRECT("$1:$"&LEN(B5))),1))*ROW(INDIRECT("$1:$"&LEN(B5))),0), ROW(INDIRECT("$1:$"&LEN(B5))))**

The **LARGE** function will now rearrange the largest values from the array according to the positions based on the numbers found in **ROW** functions. & our resultant values for this section of the formula will be-

{7;2;1;0;0;0;0;0;0}

**âž¤ MID(0&B5, LARGE(INDEX(ISNUMBER(--MID(B5,ROW(INDIRECT("$1:$"&LEN(B5))),1))*ROW(INDIRECT("$1:$"&LEN(B5))),0), ROW(INDIRECT("$1:$"&LEN(B5))))+1,1)**

Now, this part of the function will concatenate **0** with the texts in **Cell B5**. Then itâ€™ll add **1** individually with all the numbers found in the last section and show the characters from **B5 Cell** based on the defined number positions. So, our outcome from this section will be-

{â€ś2â€ł;â€ť9â€ł;â€ť1â€ł;â€ť0â€ł;â€ť0â€ł;â€ť0â€ł;â€ť0â€ł;â€ť0â€ł;â€ť0â€ť}

**Breakdown of Part C = (10^ROW(INDIRECT("$1:$"&LEN(B5)))/10),"")**

This part will determine the powers of 10 & store them inside the array. The digits of the powers are the numbers found from the ROW function previously. This part of the formula will return the values as-

{1;10;100;1000;10000;100000;1000000;10000000;100000000}

**Multiplication of B**_{n }**and C**_{n}

Now, the resultant values from the last two major breakdowns of B & C will be multiplied inside the array. Then the products found from the multiplications will be-

{2;90;100;0;0;0;0;0;0}

And finally, the **SUMPRODUCT** function will sum these values found from the array. So, our final outcome will be **192 (2+90+100+0+0+0+0+0+0)** which is the extracted numbers from **Cell B5**.

**Read More:** **How to Separate Text and Numbers in Excel (4 Easy Ways)**

**5. ****Using Flash Fill to Extract Numbers from a Text String**

Using Flash Fill is easier & simpler than any other method mentioned above. Weâ€™re going to extract numbers from any position in the text strings. To execute this method properly, we have to help Excel to find the pattern of the cell values in a column or a row by doing the extraction for the first two values only.

**Step 1:**

âž¤ In **Cell C5 & C6**, we have to extract the numbers first manually & type those values.

âž¤ Now select both** Cells C5 & C6**.

âž¤ Use **Fill Down**Â from **Cell C6 **to autofill the entire column.

**Step 2:**

âž¤ Now from the right bottom drop-down option in **Cell C12**, select **Flash Fill.**

And finally hereâ€™s your result with the extractions of all the numbers from the codes in **Column B**.

**Notes: **This method has some drawbacks thatâ€™s why itâ€™s not recommended for all cases when you need to extract numbers from text strings. **Flash Fill** usually follows a pattern from the cells in a column or a range. So, the first 2 or 3 extractions or calculations have to be done manually to help Excel absorb the common pattern of the resultant values. But sometimes, it does not follow the exact pattern we need & thereby itâ€™ll follow its own pattern & give you a mismatched result.

For example, if we had to extract two zeros(00) from the data given, itâ€™ll show only one zero, not two. Then if you want to extract numbers from the beginning or the last positions in a cell, itâ€™ll extract text values too along with the numbers.

**Read More:** **How to Extract Numbers after a Specific Text in Excel (2 Suitable Ways)**

### 6. Using VBScript to Extract Only Numbers from the Cells in Excel

If you are interested to use VBA coding to extract numbers only from the cells then youâ€™d like to follow the steps below.

**Step 1:**

âž¤ Press **ALt+F11** to open the **VBA** window.

âž¤ From the** Insert** tab, select the **Module **command. A new module window will appear where youâ€™ll type the codes.

âž¤ Now inside your module, paste the following codes after copying.

```
Sub ExtractNumbersOnly()
Dim CellValue As Range
Dim InBx1 As Range
Dim InBx2 As Range
Dim NumChar As Integer
Dim StartChar As Integer
Dim XtrNum As String
Dim DBxName1 As String
Dim DBxName2 As String
Dim Iteration As Integer
DBxName1 = "Input Data Selection"
DBxName2 = "Output Cell Selection"
Set InBx1 = Application.InputBox("Input Range of Text Cells:", DBxName1, "", Type:=8)
If TypeName(InBx1) = "Nothing" Then Exit Sub
Set InBx2 = Application.InputBox("Select Output Cell or Range of Cells:", DBxName2, "", Type:=8)
If TypeName(InBx2) = "Nothing" Then Exit Sub
Iteration = 0
XtrNum = ""
For Each CellValue In InBx1
Iteration = Iteration + 1
NumChar = Len(CellValue)
For StartChar = 1 To NumChar
If IsNumeric(Mid(CellValue, StartChar, 1)) Then
XtrNum = XtrNum & Mid(CellValue, StartChar, 1)
End If
Next StartChar
InBx2.Item(Iteration) = XtrNum
XtrNum = ""
Next CellValue
End Sub
```

**Step 2:**

âž¤ Now press **F5 **to execute the codes. A dialogue box named **Input Data Selection** will appear.

âž¤ Select all the text cells & press **OK**.

**Step 3:**

âž¤ Another dialogue box named **Output Cell Selection** will appear where you have to select the particular cell or range of cells to see the output data or values.

âž¤ Press **Enter**.

And youâ€™ll be displayed the extracted numbers from the texts at once.

**đź”Ž A Brief Explanation of the Macros:**

**âž¤ Declaring Parameters**

```
Sub ExtractNumbersOnly()
Dim CellValue As Range
Dim InBx1 As Range
Dim InBx2 As Range
Dim NumChar As Integer
Dim StartChar As Integer
Dim XtrNum As String
Dim DBxName1 As String
Dim DBxName2 As String
Dim Iteration As Integer
DBxName1 = "Input Data Selection"
DBxName2 = "Output Cell Selection"
```

Here in this part at first, weâ€™re declaring all our parameters as Integer, String Value, or Range of cells. Then we are giving names of our dialogue boxes with **â€śInput Data Selectionâ€ť & â€śOutput Cell Selectionâ€ť**.

**âž¤ Defining the Types of Inputs & Outputs for Dialogue Boxes**

```
Set InBx1 = Application.InputBox("Input Range of Text Cells:", DBxName1, "", Type:=8)
If TypeName(InBx1) = "Nothing" Then Exit Sub
Set InBx2 = Application.InputBox("Select Output Cell or Range of Cells:", DBxName2, "", Type:=8)
If TypeName(InBx2) = "Nothing" Then Exit Sub
Iteration = 0
XtrNum = ""
```

Now weâ€™re defining the parameters & their types for the dialogue boxes. Here, adding Type:=8 means the input & output data will consist of reference cells or a range of cells. Weâ€™re also defining that if input data is not found then the subroutine will stop. By mentioning this macro, the subroutine will not break down for missing data, rather itâ€™ll stop the functioning.

**âž¤ Combining the Functions Inside the Code Loops for Iterations**

```
For Each CellValue In InBx1
Iteration = Iteration + 1
NumChar = Len(CellValue)
For StartChar = 1 To NumChar
If IsNumeric(Mid(CellValue, StartChar, 1)) Then
XtrNum = XtrNum & Mid(CellValue, StartChar, 1)
End If
Next StartChar
InBx2.Item(Iteration) = XtrNum
XtrNum = ""
Next CellValue
End Sub
```

last of all, this is the most crucial part where weâ€™re applying the functions or formulas that we need to assign to the texts to find the resultant values from the strings. One of the major advantages of coding a function for Excel is itâ€™s not necessary to type a large formula like we had to do in previous methods as VBA has built-in commands to use For or While loops where iteration for each & every detail in a text string can be executed without any hassle.

**Read More: How to Separate Numbers From Text in Excel VBA (3 Methods)**

**Concluding Words**

Extracting only numbers from a text string is not as simple as it seems because it requires a combination of multiple functions which makes the final formula or syntax complicated. But I hope, how Iâ€™ve tried to illustrate the formulas by breaking down the inner functions has helped you to understand the syntax with a bit of comfort & ease. If you find any other functions or formulas which I should have added here then please let me be enlightened through your valuable comments. Or you can have a glance at our more informative & interesting articles related to the Excel functions on this website.

This is excellent, how to sum each numbers from formula output like in B5 cell if output is 192 then sum 19+2

Thanks for your feedback. You have to use another formula to do that.