How to Extract Only Numbers from Excel Cell [5 Simple Ways]

Have you ever needed to simply extract the numbers from a table of strings in Excel but didn’t know how? Then keep reading this post until the end to learn how to extract only numbers from Excel cells.

When it comes to taking a part of a text string of a certain value, Excel includes three Sequence functions (Left, Right, and Mid) to help you get the job done fast and efficiently. However, when it comes to pulling digits from a textual string, Microsoft Excel is totally void of any function whatsoever.how-to-extract-only-numbers-from-excel-cell

But it needs a little creativity, a little effort, and a slew of various functions layered inside of each other to extract a number from a string in Microsoft Excel.

Even though Microsoft Excel does not give a straightforward formula or syntax for pulling just numbers from cells, we may combine a variety of Excel formulae to create a single function that can be used to extract just numbers or digits from cells in Excel.

In this post, I will try to demonstrate and explain in detail how we may extract just numbers from cells that contain the required formulas when certain conditions are met.

So, keep reading this post to learn more in detail.

5 Ways to Extract Only Numbers from Excel Cell

Now we are going to show you some of the most effective and easy ways to extract only numbers from Excel cells. The methods are given below:

1. Extract Numbers from Text String Using the TEXTJOIN Function

First and foremost, we are going to show you the newly released TEXTJOIN function, which is specially made for the later versions of Excel 2016. Because it makes use of the newly added TEXTJOIN feature, this formula will only run with Excel 2016.

Aside from that, this method can be used to pull the numbers that appear at the start, end, or center of a string of text.

However, you should also keep in mind that the TEXTJOIN method described in this part will combine all of the numeric characters into a single string of characters. To clarify, the output will be 30300 if the text is “The price of 20 tickets is USD 300.”

Now, let’s apply the formula practically. Consider this dataset: assume you have the following texts in each cell, and you want to get the digits from each string:

The following formula will help you to extract the numeric portion of the string from an Excel sheet.

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

textjoin-function-excel

Because this is an array formula, you must use the keyboard shortcut Control + Shift + Enter rather than just pressing Enter.

In the event that the text string has no numbers, this formula will yield a blank value (empty string). You can replace the blank cells with 0 if you want.

So, now that you got the formula, it’s time to understand how it works.

Allow me to breakdown this formula and attempt to explain how this formula works:

  • ROW(INDIRECT(“1:”&LEN(A2))): As a result of this portion of the formula, a sequence of numbers beginning with one would be generated. Using the LEN function in the formula, you can find out how many characters are in the string. As an example, it will yield 19 in the situation of “The cost is USD 100.” Accordingly, ROW(INDIRECT(“1:19”) would be used in the formulae.

When the ROW function is used, it will return a sequence of integers starting with 1 and ending with 19: 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19.

  • (MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1): In this phase of the algorithm, you would get an array of #VALUE! errors or integers dependent on the text you entered. All of the text characters in the string are converted to #VALUE! Errors, but all of the numerical values remain unchanged. This is due to the fact that we have increased the MID function by 1.
  • IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””): When the IFERROR function is called, it will delete all of the #VALUE! Errors from the code, leaving just the numbers behind. The output of this part would look like this: {“”;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;1;0;0}.
  • =TEXTJOIN(“”,TRUE,IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””)): The TEXTJOIN function now merely joins together the string characters that are left (which are just the integers) and skips the empty string that was previously used.

Bit of advice: To test the output of a portion of your formula, first select the cell, then hit F2 to enter the edit mode. Then pick the portion of your formula upon which you want to get the result, and click F9. You will be able to view the results immediately. However, make sure to either click Control + Z or click the Escape key when you’ve finished. DO NOT press the Enter key at any point.

However, if you want to extract only the text part from an alphanumeric string, you can apply the exact logic. The formula is given below:

=TEXTJOIN(“”,TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1),””))

The IF function is used to determine whether or not the array returned by the MID function contains errors. This is a slight modification from the previous formula. If there is an error, the value is kept; otherwise, the value is replaced with a blank.

Finally, to merge all of the alphanumeric characters, the TEXTJOIN function is used.

Warning: Although this formula is quite effective, it makes use of an unstable function (the INDIRECT function). This indicates that if you use it with a huge database, it may take a bit of time for the results to be returned to your screen. So, it is recommended that you make a backup before using this function in Excel.

2. Extract Numbers from The Beginning By Using the Mix of LEFT, SUM, LEN & SUBSTITUTE Functions

If you are using the prior version of Excel 2016, then the previous method we showed above won’t work for you. In this case, you will have to use this alternative consequently.

In this method, we are going to combine and mix some functions and create our own formula. We will use the LEFT, SUM, LEN & SUBSTITUTE functions.

As we can see in the screenshot below, we have a dummy spreadsheet with two columns, one is for the text, and the other one is for extracting the numbers only.

So, the process of how we extracted the numbers only are given below:

  • Select the cell C3 and enter the formula given below:

=LEFT(B3,SUM(LEN(B3)-LEN(SUBSTITUTE(B3,{“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″},””))))

  • Once you typed or pasted the formula in the C3 cell, hit the Enter button on your keyboard, and the number 34 will appear on the cell.
  • Now hold the plus icon (Fill Handle) located at the bottom right corner and drag it down all the way to the C10 cell. And all the remaining cells will be auto-filled.

left-sum-len-substitute

So, now that you found a readymade formula and done your job, it’s time to understand how these functions work. Allow us to explain it.

Explanation: This formula works in many different ways. The procedures are:

  • SUBSTITUTE(B3,{“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″},””): In this case, the SUBSTITUTE function will search for the numbers from 0 to 9 in a sequential manner, and, if it finds one, it will substitute that number in Cell B3 with a blank character on each occasion. Thus, the function will return as follows: {“34DTXRF”,”34DTXRF”,”34DTXRF”,”4DTXRF”,”3DTXRF”,”34DTXRF”,”34DTXRF”,”34DTXRF”,”34DTXRF”,”34DTXRF”}
  • LEN(SUBSTITUTE(B3,{“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″},””)): Using the LEN function, you may find out how many characters are in a string. As a result, the LEN function will calculate all of the characters that were discovered in the texts separately using the SUBSTITUTE function in this case. In this situation, the resulting values will be as follows: {7,7,7,6,6,7,7,7,7,7}
  • LEN(B3)-LEN(SUBSTITUTE(B3,{“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″},””))): This portion of the formula is just the subtraction of the number of characters found in Cell B3 from the total number of characters found separately in the preceding section of the calculation. As a consequence, the values that will be obtained will be- {0,0,0,1,1,0,0,0,0,0}
  • SUM(LEN(B3)-LEN(SUBSTITUTE(B3,{“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″},””))): The SUM operator will then just sum all of the deducted numbers discovered, and therefore the result here is = 2 (0+0+0+1+1+0+0+0+0+0).
  • =LEFT(B3,SUM(LEN(B3)-LEN(SUBSTITUTE(B3,{“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″},””)))): And now comes the last portion of the formula, where the LEFT function will give the values that include the precise amount of characters from the left that was determined in the preceding part of the formula. Because we obtained the total value of 2 from the text 34DTXRF, the LEFT function will return just the number 34 from the string 34DTXRF.

And that’s how this formula works.

3. Extract Numbers from the Right of a Text by Combining RIGHT, LEN, MIN & SEARCH Functions

The previous method we showed only works for extracting numbers from left to right. So, if your number begins from the left, then the previous method won’t be any good for you. So, in this method, we are going to show you another way to extract numbers from integers which will extract from the right to left of a text string.

To do that, follow the below steps carefully:

  • Select the cell C3 and enter the formula given below:

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

  • Once you typed or pasted the formula in the C3 cell, hit the Enter button on your keyboard, and the number 34 will appear on the cell.
  • Now hold the plus icon (Fill Handle) located at the bottom right corner and drag it down all the way to the C10 cell. And all the remaining cells will be auto-filled.

right-len-min-search

So, now that you found a readymade formula and done your job, it’s time to understand how these functions work. Allow us to explain it.

Explanation: This formula works in many different ways. The procedures are:

  • B3&”0123456789″: In this part of the formula, we are using the ampersand (&) to join the data in the B3 Cell with 0123456789, and the resulting value will be: DTXRF340123456789
  • SEARCH({0,1,2,3,4,5,6,7,8,9}, B3&”0123456789″): SEARCH will now look for any and all numbers from 0 to 9 one by one in the resulting value obtained in the earlier part and will provide the placements of all those numbers in the digits of the DTXRF340123456789 string as the outcome of its search. As a consequence, the following will be our final values: {8,9,10,6,7,13,14,15,16,17}
  • MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, B3&”0123456789″)): The MIN operator is often used to identify the digit or integer that is the smallest in an array. This means that 6 will be the smallest or lowest value in this case, which was determined by the array of numbers 8, 9, 10, 6, 7, 13, 14, 15, 16, and 17 that was previously mentioned in the formula.
  • LEN(B3) – MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, B3&”0123456789″)) +1): Now, the main role of the LEN function will be to determine the total number of words in the B3 cell. Then it will deduct the number 6 (which was discovered in the previous part) and then send it back the result by adding 1. In this particular instance, the resulting value will be 2(7-6+1).
  • =RIGHT(B3,LEN(B3) – MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, B3&”0123456789″)) +1): When using the RIGHT function, it will provide a set of characters that will be returned from the right side of a string. Based on the result generated by the deduction method described in the earlier chapter, the RIGHT call will display the last two characters from Cell B3, which will be the number 34 in this case.

And in this way, the RIGHT, LEN, MIN, and SEARCH function works in a formula.

4. Extract Numbers from Any Part of a Text String

And now, finally, there is a solution that will work in every circumstance. A text string can also include any amount of digits or integers in it, and this function can extract them from any place. By any place, I mean any, including extracting data from a website.

If your datasheet doesn’t match with our demo sheet and doesn’t know how to extract numbers from a string cell, then this method is going to save you. By following this method, you can easily extract numbers from a string from every direction.

Note:: However, this method will only work if you are using the Excel 2016 version or higher. Otherwise, this function won’t work.

Hence, to do that, follow the below steps carefully and enter the following formula in your target cell:

  • Select the cell C3 and enter the formula given below:

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

  • Once you typed or pasted the formula in the C3 cell, hit the Enter button on your keyboard, and the number 34 will appear on the cell.
  • Now hold the plus icon (Fill Handle) located at the bottom right corner and drag it down all the way to the C10 cell. And all the remaining cells will be auto filled.

extract-numbers-from-any-part

So, now that you found a readymade formula and done your job, it’s time to understand how these functions work. Allow us to explain it.

Explanation: This formula works in many ways. The procedures are:

  • ROW(INDIRECT(“1:”&LEN(B3))): The ROW function is often used to determine the number of rows of a cell. However, since no target cell has been specified in the INDIRECT function in this situation, the ROW function will pull all of the integers or digits from the reference texts that have been put in the INDIRECT function. Therefore, for the first Cell B3, the values obtained via the use of the ROW and INDIRECT procedures will be as follows: {1;2;3;4;5;6;7;8;9}
  • (MID(B3,ROW(INDIRECT(“1:”&LEN(B3))),1)): The MID function shall allow you to find the letters that are in the center of a string of text given a beginning point and length. In this case, for each of the nine spots determined in the preceding part, the MID function will now display all of the characters one by one for every spot, returning the values as follows: {“1″;”9″;” “;”D”;”D”;”X”;”2″;”M”;”N”}
  • IFERROR((MID(B3,ROW(INDIRECT(“1:”&LEN(B3))),1)*1),””): In this case, the IFERROR function will check whether or not a character is an integer or anything else. Unless it can distinguish between a string of numbers or digits, it will return the value with a textual command that has been specified.

When we use the IFERROR function, all of the values discovered in the previous section would be increased by one, and if the outputs return as value errors for characters or textual values, which can be increased, the errors will be converted into blank strings.

As a consequence, our final values will be as follows: {1;9;””;””;””;””;2;””;””}

  • =TEXTJOIN(“”,TRUE,IFERROR((MID(B3,ROW(INDIRECT(“1:”&LEN(B3))),1)*1),””)): This formula will now be followed by the TEXTJOIN function, which will complete the last stage of the process. Mainly this function is utilized to combine multiple or connect two strings together. As a consequence, the resulting values that we discovered in the previous part will now be combined together by the TEXTJOIN function that we have implemented.

This gives us the number 192 as the result of our calculations.

And in this way, you can extract numbers from any direction.

5. Extract Numbers from Any Position in a Cell with the Advanced Formula

Now, here comes an advanced formula that you were looking for. This formula is going to be very long. By following this method, no matter what version of your Excel is, it will work like a charm. However, at first glance, you may get startled by seeing how long and complicated the formula is! But believe me, when you thoroughly grasp the explanation, it will look like a regular formula to you.

Despite the fact that it seems to be more complicated, I will break down the entire procedure and attempt to explain all of the compact operations in simple terms.

Hence, to do that, follow the below steps carefully and enter the following formula in your target cell:

  • Select the cell C3 and enter the formula given below:

=IF(SUM(LEN(B3)-LEN(SUBSTITUTE(B3, {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9”}, “”)))>0,

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

ROW(INDIRECT(“$1:$”&LEN(B3))),0), ROW(INDIRECT(“$1:$”&LEN(B3))))+1,1) *

10^ROW(INDIRECT(“$1:$”&LEN(B3)))/10),””)

  • Once you typed or pasted the formula in the C3 cell, hit the Enter button on your keyboard, and the number 192 will appear on the cell.
  • Now hold the plus icon (Fill Handle) located at the bottom right corner and drag it down all the way to the C10 cell. And all the remaining cells will be auto-filled.

extract-numbers-with-advanced-formula

So, now that you found a readymade formula and done your job, it’s time to understand how these functions work. Allow us to explain it.

Explanation: This formula works in many different ways. And there are a lot of sections. So, for your convenience, it would be great if we break the formula into three different groups and explain it separately. We are going to name those parts A, B, and C. Those parts are:

Part A = SUM(LEN(B3)-LEN(SUBSTITUTE(B3, {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9”}, “”

Part B = MID(0&B3, LARGE(INDEX(ISNUMBER(–MID(B3,ROW(INDIRECT(“$1:$”&LEN(B3))),1))*

ROW(INDIRECT(“$1:$”&LEN(B3))),0), ROW(INDIRECT(“$1:$”&LEN(B3))))+1,1)

Part C = 10^ROW(INDIRECT(“$1:$”&LEN(B3)))/10),””

Now let’s move on to the main explanation.

Explanation of Part A = SUM(LEN(B3)-LEN(SUBSTITUTE(B3, {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9”}, “”

In this part, we are going to explain part A of the formula. Below is the total breakdown of it:

  • SUBSTITUTE(B3, {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9”}, “”): The SUBSTITUTE function can search for all numbers from 0 to 9 in the string 19 DDX2MN one after the other, one at a time, and will substitute these numbers with an empty string at the places of the numbers that were found. As a consequence, the entries in an array that are produced will be- {“19 DDX2MN”,”9 DDX2MN”,”19 DDXMN”,”19 DDX2MN”,”19 DDX2MN”,”19 DDX2MN”,”19 DDX2MN”,”19 DDX2MN”,”19 DDX2MN”,”1 DDX2MN”}
  • LEN(SUBSTITUTE(B3, {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9”}, “”)): In this part, the LEN function shall calculate the number of letters in all text values that were acquired in the earlier section. This function will return the following results: {9,8,8,9,9,9,9,9,9,8}
  • LEN(B3)-LEN(SUBSTITUTE(B3, {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9”}, “”)): Next, in this phase of the procedure, the number of characters from Cell B3 will be used to deduct every one of the values from the previous section. The numbers that will arise from this will be as follows: {0,1,1,0,0,0,0,0,0,1}
  • SUM(LEN(B3)-LEN(SUBSTITUTE(B3, {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9”}, “”))): The numbers included within the array discovered in the previous section would add up to 3 (0+1+1+0+0+0+0+0+0+1) with the use of the SUM function.

Consequently, A>0 (3>0) is true according to the above half of our formula. We’ll then go on to the following part of the breakdown, which is:

Explanation of Part B = MID(0&B3, LARGE(INDEX(ISNUMBER(–MID(B3,ROW(INDIRECT(“$1:$”&LEN(B3))),1))*

ROW(INDIRECT(“$1:$”&LEN(B3))),0), ROW(INDIRECT(“$1:$”&LEN(B3))))+1,1)

  • INDIRECT(“$1:$”&LEN(B3)): The INDIRECT function will be used to save the string values as a pointer to the arrays in this case. The Apostrophe(&) command will link the set of characters discovered in Cell B3 with the formula for the Number of Cells command located within the bracket. It signifies that the number of characters specified will be saved as an array value starting at 1 and increasing until the number of characters declared is reached.
  • ROW(INDIRECT(“$1:$”&LEN(B3))): Next, using the ROW function, all of the integers in the array will be extracted, and the value obtained for Cell B3 will be as follows: {1;2;3;4;5;6;7;8;9}
  • MID(B3,ROW(INDIRECT(“$1:$”&LEN(B3))),1): The MID function will be used in this section of the equation to represent all of the letters from Cell B3 based on all of the spots that were identified as integers in the preceding part. As a result, the numbers that have been extracted would be found as follows: {“1″;”9″;” “;”D”;”D”;”X”;”2″;”M”;”N”}
  • ISNUMBER(–MID(B3,ROW(INDIRECT(“$1:$”&LEN(B3))),1)): Because ISNUMBER is a mathematical function, it will decide if the entries in the previous section are integer strings. If yes, it returns TRUE; else, it returns FALSE. So, in our instance, the output is: {TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}
  • INDEX(ISNUMBER(–MID(B3,ROW(INDIRECT(“$1:$”&LEN(B3))),1))*ROW(INDIRECT(“$1:$”&LEN(B3))),0): As you can see, the above function uses a double-hyphen which is also called Double Unary. It usually converts logical values to 1 (TRUE) or 0 (FALSE). The INDEX function now returns a value as- {1;1;0;0;0;0;1;0;0}

Then the resulting values are multiplied by the numbers from the ROW function within the array, yielding the result: {1;2;0;0;0;0;7;0;0}

  • LARGE(INDEX(ISNUMBER(–MID(B3,ROW(INDIRECT(“$1:$”&LEN(B3))),1))*ROW(INDIRECT(“$1:$”&LEN(B3))),0), ROW(INDIRECT(“$1:$”&LEN(B3)))): This LARGE operator now will reorganize the array’s highest values depending on ROW function locations. For this portion of the formula, we get: {7;2;1;0;0;0;0;0;0}
  • MID(0&B3, LARGE(INDEX(ISNUMBER(–MID(B3,ROW(INDIRECT(“$1:$”&LEN(B3))),1))*ROW(INDIRECT(“$1:$”&LEN(B3))),0), ROW(INDIRECT(“$1:$”&LEN(B3))))+1,1): This portion of the procedure then concatenates 0 with characters in Cell B3. Then it adds 1 to each of the previous numbers and displays the letters from B3 Cell depending on the set integer locations. So, this segment’s output would be- {“2″;”9″;”1″;”0″;”0″;”0″;”0″;”0″;”0”}

Explanation of Part C = 10^ROW(INDIRECT(“$1:$”&LEN(B3)))/10),””

Now, after completing all the parts above, the formula in this part finds the factors of 10 and keeps these in the array. The factors’ numbers are the ROW function’s values. The formula returns the values as-

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

After the final two significant breakdowns of B and C, the resulting values would be multiplied together in an array. Then, the multiplied products would be-

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

SUMPRODUCT will then total all of the numbers in the array. This gives us a final result of 192, which is calculated as 2+90+100+0+0+0+0+0+0, which is equal to the sum of the values in cell B3.

And in this way, this crazy and advanced formula works. If you are an expert in MS Excel, then you should really go for it.

Conclusion

Since numerous functions are involved, the final formula or syntax for extracting just numbers from a string of text is not as straightforward as it looks. However, I believe that the way I have broken out the internal functions of the formulae in this article has made it easier for you to grasp the syntax. We hope this article has helped you understand how to extract only numbers from Excel cells.

Related Posts