How to Extract Only Numbers from Excel Cell (7 Easy Ways)

Formula Breakdown

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

• The SUBSTITUTE function will find the digits (0-9) consecutively and, if found, it will replace that digit in cell B5 with an 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”},””))

• The 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”},””)))

• 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”},” ))))

• The LEFT 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 34 from the text 34DTXRF.

Formula Breakdown

➤ B5&”0123456789″

• 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″)

• The SEARCH 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 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 function is used to find the lowest digit or number in 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)

• 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) and 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. The RIGHT function will show the last 2 characters from cell B5, and that’ll be 34.

Formula Breakdown

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

• The INDIRECT function is used to store an array of cell values as a reference text. Here the ampersand (&) command concatenates the length of the characters of cell B5 with incomplete range syntax (1:).
• The INDIRECT function will store all the numbers between 1 and the length of the characters in cell B5 as a reference text.

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

• The ROW function usually tells the row number of a cell. But here 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.
• For the cell B5, the resultant values through these ROW and 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.
• 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),””)

• 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 are returned as value errors 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),””))

• This function is used to concatenate or join two strings with a specified delimiter.
• The resultant values we have found in the preceding section will now be joined together alongside this TEXTJOIN function. We’ll get the number 192.

Formula Breakdown

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

=IF(A>0, SUMPRODUCT(B₁*C₁, B₂*C₂, ……….B_nC_n),””)

This syntax means if A is greater than 0, then all the products of B_n and 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”}, “”)

• The SUBSTITUTE function will find all digits (0-9) one by one in the text 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 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).
• According to the first part of our formula, A>0 (3>0). 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 to the array. Inside the parenthesis, the ampersand (&) command will join the number of characters found in cell B5 with the Range of cells’ syntax. It means that from 1 to the number of characters defined, each will be stored as an array reference.

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

• 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)

• 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, 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.
• 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 the 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)

• 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.
• 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

• 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}.
• The SUMPRODUCT function will sum these values found in 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.

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.
  • We are adding all the values to extract five digit numbers only.

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. 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.

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"

• 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 = ""

• 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

• 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.
• 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.