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

**Watch Video – Extract Only Numbers from Excel Cell**

There will be one VBA code, one Excel feature, and five practical formulas to help you extract numbers from a cell. Like in the picture below, we have some codes including digits and letters where digits are present at the beginning. We have to extract those digits or numbers only.

## 1. Combining LEFT, SUM, LEN, and SUBSTITUTE Functions to Extract Numbers Only from the Beginning of Text in Excel Cell

In this first method, we will combine the **LEFT**, **SUM**, **LEN**, and **SUBSTITUTE** functions to separate numbers from the beginning of a text string. Firstly, we will type this formula in the cell, and then, using **the Fill Handle**, we will copy that formula to the rest of the cells.

**Steps:**

- Firstly, type the formula in cell
*C5*

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

- Secondly, press
*Enter*

- Thirdly, use the
then to autofill all other cells in column*Fill Handle*.*C*

**🔎** **Formula Breakdown**

➤ **SUBSTITUTE(B5,{“0”, “1”, “2”, “3”, “4”, “5”, “6”, “7”, “8”, “9”},””)**

- Here, the
function will find the digits (0-9) consecutively and, if found, it will replace that digit in cell*SUBSTITUTE**B5*

➤ **LEN(SUBSTITUTE(B5,{“0”, “1”, “2”, “3”, “4”, “5”, “6”, “7”, “8”, “9”},””))**

- The
*LEN*function will count all the characters individually found in the texts through the*LEN*function. The resultant values will be here in our case – {7,7,7,6,6,7,7,7,7,7}.*SUBSTITUTE*

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

➤ **SUM(LEN(B5)-LEN(SUBSTITUTE(B5,{“0”, “1”, “2”, “3”, “4”, “5”, “6”, “7”, “8”, “9”},””)))**

- The
*SUM***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
function will return the values with an exact number of characters from the left found in the previous section of the formula. As we got the sum value as 2, the*LEFT*function here will return only*LEFT***34**from the text**34DTXRF**.

## 2. Joining Excel RIGHT, MIN, and SEARCH Functions to Extract Only Numbers from the Right Side of Text in the Cell

In this section, we’ll extract the numbers or digits from the right side of the text string. We’ll use the **RIGHT**, **MIN**, and **SEARCH** functions here.

**Steps:**

- To begin with, in our dataset what we have to type in cell
*C5*

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

- Afterward, press
and then use the*Enter*to autofill the rest of the cells.*Fill Handle*

**🔎** **Formula Breakdown**

➤ **B5&”0123456789″**

- Here, we’re concatenating values in the
cell with*B5***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
function will search for all the digits (0-9) one by one in the resultant value obtained from the previous section and will return the positions of those 10 digits in the characters of*SEARCH***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″))**

- The
*MIN***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*function. Then it’ll subtract the value 6(found in the last section) and then return the result by adding 1. Here in our case, the resultant value will be*LEN***2 (7-6+1)**.

➤ **RIGHT(B5,LEN(B5) – MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, B5&”0123456789″)) +1)**

- The
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*RIGHT**B5***34**.

## 3. Merging Excel TEXTJOIN, IFERROR, and INDIRECT Functions to Extract Numbers from Any Part of a Text String

Now, here’s a broad solution for all cases. This method will pull out numbers or digits from any position in a text string. Moreover, we will use the **TEXTJOIN**, **IFERROR**, **INDIRECT**, **MID**, and **ROW** functions in this method.

**Steps:**

- Firstly, type the formula in your destination cell as follows-

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

- Then, if you’re using
**Excel 2016**or higher version press, otherwise press*Enter*to get the result for this array formula.*Ctrl+Shift+Enter* - After this step, autofill other cells using the
and you’re done.*Fill Handle*

**🔎**** Formula Breakdown**

➤ **INDIRECT(“1:”&LEN(B5))**

- The
*INDIRECT***(&)**command concatenates the length of the characters of cellwith incomplete range syntax*B5***(1:)**. - So, here the
function will store all the numbers between 1 and the length of the characters in cell*INDIRECT*as a reference text.*B5*

➤ **ROW(INDIRECT(“1:”&LEN(B5)))**

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

➤ **(MID(B5,ROW(INDIRECT(“1:”&LEN(B5))),1))**

- The
*MID* - So, here for all 9 positions found in the previous section, the
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”}.*MID*

➤ **IFERROR((MID(B5,ROW(INDIRECT(“1:”&LEN(B5))),1)*1),””)**

- Now, the
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.*IFERROR* - In our case, all the values found in the last section will be multiplied by 1, and when the results are returned as value errors for letters or text values that cannot be multiplied, their
*IFERROR*

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

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

## 4. Nesting Multiple Functions to Extract Only Numbers from Excel Cell

Now, we will show you another formula to extract only numbers from any position from an Excel cell. Although it may seem quite complex, we’ll break down the whole formula and try to explain all the compact functions with ease. Additionally, we will use the **IF**, **LARGE**, **INDEX**, **SUMPRODUCT**, and **ISNUMBER** functions in this formula.

- To begin with, type this formula in cell
. You have to replace the cell reference only based on your cell in the spreadsheet and then by embedding this formula, you’ll get the expected result right away. And this formula works perfectly in any version of Excel.*C5*

`=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),"")`

**
**

- After that, you have to press
only after typing the entire formula and you’re done.*Enter*

**🔎** **Formula Breakdown**

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}** and

**C**

**will sum up to the final result. And if**

_{n}**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”}, “”)**

- The
function will find all digits (0-9) one by one in the text*SUBSTITUTE***19 DDX2MN**each time and will replace those digits with an empty string in the positions 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”}, “”))**

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

➤ **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
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}.*B5*

➤ **SUM(LEN(B5)-LEN(SUBSTITUTE(B5, {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9”}, “”)))**

- With the help of the
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).*SUM* - 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***(&)**command will join the number of characters found in cellwith the Range of cells’ syntax. It means that from 1 to the number of characters defined, each will be stored as an array reference.*B5*

➤ **ROW(INDIRECT(“$1:$”&LEN(B5)))**

- Now, this
function will pull out all the numbers from the array and the resultant values for cell*ROW*will be- {1;2;3;4;5;6;7;8;9}.*B5*

➤ **MID(B5,ROW(INDIRECT(“$1:$”&LEN(B5))),1)**

- In this part of the formula, the
function will express all the characters from cell*MID*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”}.*B5*

➤ **ISNUMBER(–MID(B5,ROW(INDIRECT(“$1:$”&LEN(B5))),1))**

- As
is a logical function, it’ll determine individually if the values found in the preceding section are number strings or not. If yes, then it’ll return as*ISNUMBER***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, thefunction will return this result as- {1;1;0;0;0;0;1;0;0}.*INDEX* - After that, the resultant values will be multiplied by the values obtained from the
function inside the array and the outcome will be- {1;2;0;0;0;0;7;0;0}.*ROW*

➤ **LARGE(INDEX(ISNUMBER(–MID(B5,ROW(INDIRECT(“$1:$”&LEN(B5))),1))*ROW(INDIRECT(“$1:$”&LEN(B5))),0), ROW(INDIRECT(“$1:$”&LEN(B5))))**

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

➤ **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. Then it’ll add*B5***1**individually with all the numbers found in the last section and show the characters fromcell based on the defined number positions.*B5* - 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
function previously.*ROW* - 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 and C will now 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
function will sum these values found in the array. So, our final outcome will be*SUMPRODUCT***192 (2+90+100+0+0+0+0+0+0)**, which is the extracted numbers from cell.*B5*

## 5. Combining Excel CONCAT and SEQUENCE Functions to Extract Five Digit Numbers Only from Cell String

We will use another formula to extract five digits numbers from any part of a string in Excel. We’ll use the **CONCAT** and **SEQUENCE** functions for the first time in this section. Moreover, we have changed our dataset slightly for this method.

**Steps:**

- Firstly, select the cell range
.*C5:C12* - Secondly, type the following formula.

`=CONCAT(IFERROR(0+MID(B5,SEQUENCE(LEN(B5)),1),""))`

- Finally, press
.*Ctrl+Enter*

**🔎** **Formula Breakdown**

- LEN(B5)
**Output: 11**.- This function returns the length of the string.

- SEQUENCE(11)
**Output: {1;2;3;4;5;6;7;8;9;10;11}**.- This function returns the first eleven numbers.

- MID(B5,{1;2;3;4;5;6;7;8;9;10;11},1)
**Output: {“1″;”9″;” “;”D”;”D”;”X”;”2″;”M”;”N”;”3″;”3″}**.- Using this part, we are getting the individual characters from the string.

- 0+{“1″;”9″;” “;”D”;”D”;”X”;”2″;”M”;”N”;”3″;”3″}
**Output: {1;9;#VALUE!;#VALUE!;#VALUE!;#VALUE!;2;#VALUE!;#VALUE!;3;3}**.- When we add zero with a string, it will return an error.

- IFERROR({1;9;#VALUE!;#VALUE!;#VALUE!;#VALUE!;2;#VALUE!;#VALUE!;3;3},””)
**Output: {1;9;””;””;””;””;2;””;””;3;3}**.- We are getting blank for all error values.

- CONCAT({1;9;””;””;””;””;2;””;””;3;3})
**Output: 19233**.- Finally, we are adding all the values to extract five digit numbers only.

## 6. Using Excel Flash Fill to Extract Numbers Only Within a Cell Range

Using **the** **Flash Fill** feature is easier and 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 find the pattern of the cell values in a column or a row by doing the extraction for the first two values only.

**Steps:**

- To begin with, type the numbers manually in cell
.*C5*

- Then, start typing the numbers from cell
to cell*B6*and Excel will automatically recognize the pattern.*C6* - Finally, press
.*Enter*

**Notes: **This method has some drawbacks, which is why it’s not recommended for all cases when you need to extract numbers from text strings. The **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 and, thereby, it’ll follow its own pattern and give you a mismatched result.

For example, if we had to extract two zeros (00) from the data given, it’d 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.

## 7. Applying VBA Code to Extract Only Numbers from Excel Cell

If you are interested in using the **Excel VBA Macro** to Remove Numbers from String, then you’d like to follow the steps below. We will show you how to type the code in the **VBA Module** window. This code will ask the user to specify the input and output cell ranges.

**Steps:**

- Firstly, Press
to open the*ALT+F11*window.*VBA* - Then, from the
*Insert*command. A new module window will appear where you’ll type the codes.*Module*

- Thirdly, inside your module, paste the following codes after copying.

```
Option Explicit
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 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
```

- Afterward, press
to execute the code. A dialogue box named “*F5***Input Data Selection**” will appear. - Then, select all the text cells (i.e.
) and press*B5:B12*.*OK*

- After that, another dialogue box named “
**Output cell Selection**” will appear where you have to select a particular cell or range of cells to see the output data or values. - Finally, select the cell range
and press*C5:C12*.*Enter*

- Consequently, you’ll see the extracted numbers from the texts all at once. Thus, we will finish the seven quick methods to extract numbers only from Excel cell.

**🔎** **VBA Code Breakdown**

**➤**** Declaring Parameters**

```
Option Explicit
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 integers, string values, or ranges of cells. Then we are giving the names of our dialogue boxes with
**“Input Data Selection”**and**“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 Cells:", _
DBxName2, "", Type:=8)
If TypeName(InBx2) = "Nothing" Then Exit Sub
Iteration = 0
XtrNum = ""
```

- Now we’re defining the parameters and their types for the dialogue boxes. Here, adding Type:=8 means the input and 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, but rather it’ll stop 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 that 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 and every detail in a text string can be executed without any hassle.

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 and find the results immediately through embedded formulas.

## Conclusion

We have shown you 7 easy methods to extract only numbers from an Excel cell. 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 we hope how we’ve tried to illustrate the formulas by breaking down the inner functions has helped you to understand the syntax with a bit of comfort and ease.

If you find any other functions or formulas that we should have added here, then please let us know through your valuable comments. Or you can have a glance at our more informative and interesting articles related to Excel functions on this website.

**<< Go Back to Separate Numbers Text | Split | Learn Excel**

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.

Semi-Qualifié 3 = SQ3

Semi-Qualifié 1 = SQ1

Manœuvre Spécialisé = MS

what is formula to simplyfy the words each first letter and combine like =

Hello Theva,

Glad that you shared your query. As per your given example, if you want to simplify the first letter of each word along with numbers, apply this simple VBA code.

`Function ExtractFirstLetters(rng As Range) As String`

`Dim arry`

`Dim X As Long`

`arry = VBA.Split(rng, " ")`

`If IsArray(arry) Then`

`For X = LBound(arry) To UBound(arry)`

`ExtractFirstLetters = ExtractFirstLetters & Left(arry(X), 1)`

`Next X`

`Else`

`ExtractFirstLetters = Left(arry, 1)`

`End If`

`End Function`

Note that, if the words in your dataset are separated by

Hyphens(–) then insert this line in the code`arry = VBA.Split(rng, "-")`

Instead of this,

`arry = VBA.Split(rng, " ")`

I hope this will solve your problem. Let us know your feedback.

Regards,

Guria

ExcelDemy.

thank you so much

Dear

Yasin,You are most welcome.

Regards

ExcelDemyHello:

not work in my case since I need the minimum value in the same cell which si something like this:

Functional Test.: 4

In- Circuit Test: 4

Inspection, Visual: 7

Run-in Test: 4

Hello

OLIVER,I hope you are doing well. Thank u for your query. Well, you can use the below formula to extract only the minimum value in the same cell.

`=MIN(IFERROR(VALUE(MID($B$4:$B$8, FIND(":", $B$4:$B$8) + 1, LEN($B$4:$B$8))), ""))`

Note:Change the range(B4:B8)according to your dataset.Hope this information will help you. Please let us know if there is any further query in the comment section.

Best Regards,

Afrina NafisaExceldemyThank you for providing these solutions! I get an error when I use the 3rd option, specifically when using the asterisk with “1”: textjoin(“”,true,iferror((mid([cellref],row(indirect(“1:”&LEN([cellref]))),*1),””))

Any tips?

Hello

ScubaCamper,Thanks for your appreciation. You are getting the error with asterisk because your formula attempts to multiply by 1 (*1) directly within the MID function’s parameters, that is not correct. You need to apply the multiplication outside of the MID function but within the array operation.

Please try this formula:

=TEXTJOIN(“”, TRUE, IFERROR(MID(B5, ROW(INDIRECT(“1:” & LEN(B5))), 1)* 1, “”))In your formula style the formula would be:

textjoin(“”,true,iferror((mid([cellref],row(indirect(“1:”&LEN([cellref]))),1)*1),””))The purpose of multiplying by 1 (*1) in Excel formulas often is to convert text numbers to actual numeric values.

Regards

ExcelDemyHello, thankyou for putting this tutorial together.

How would I do this including decimal points? and if I had separate numbers in-between each string is there a way to send them to different columns?

Regards,

Harry

Hello

Harry IngramThanks for such an interesting comment. You wanted to extract numbers even if there are decimal points. Additionally, if there are more numbers, you want to put them in different columns. I have come up with a solution using several Excel VBA User-defined functions.

SOLUTION Overview:To do so, follow these steps:I have also attached the solution workbook; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

Harry Ingram,You can use this formula to extract only the numbers, including the decimal point.

=TEXTJOIN(“”, TRUE, IFERROR(IF(ISNUMBER(SEARCH(MID(B5, ROW(INDIRECT(“1:” & LEN(B5))), 1), {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″,”.”})), MID(B5, ROW(INDIRECT(“1:” & LEN(B5))), 1), “”), “”))

Feel free to download the Excel file from the link below, you’ll find examples of the formula.

Excel file:

Extract Decimal Point NumbersRegards,

ExcelDemy