0

I wanted to remove all letters from a cell, leaving only numbers.After some googling, I found this array formula: =SUM(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)

When I click evaluate, it makes hundreds of true/false statements which are extremely confusing. Can someone break this down and explain what each component does?

2 Answers 2

1

Let's step through the Evaluate process together:

In my example, I have the value abf5fb6 in cell A2, which evaluates to 56.

First step, replace A2 with value at cell A2

Before:

=SUM(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)

After:

=SUM(MID(0&"abf5fb6",LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)

Notice how the text now in enclosed inside a pair of quotation marks. This means that it is text, otherwise known as a string.

Now is a good time to briefly explain the MID function. This function will simply extract some text from a string. The first argument is the text, or string, that we will start with. The second argument is the start position of where we want to start extracting. The third argument is the number of characters we want to extract for, or the length of our ending result. For example, =MID("wizlog", 1, 3 will return wiz while =MID("wizlog", 2, 5) will return izlog

So, the next step then concatenates the 0&"abf5fb6", because as noted above, the first argument in the MID function requires text (again, called a string). But in order to add a number to a string in Excel, you use the & symbol.

Before:

=SUM(MID(0&"abf5fb6",LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)

After:

=SUM(MID("0abf5fb6",LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)

Now, we will jump to the next MID function to replace the other A2 with it's value again, just like before.

After:

=SUM(MID("0abf5fb6",LARGE(INDEX(ISNUMBER(--MID("abf5fb6,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)

Next, we're dealing with the second argument in the second MID function, ROW($1:$99). Remember, the second argument in the MID function just gives us the starting position. The ROW function on the other hand simply just returns the row it is given, so since we pass it the range of 1-99, then it will return to us an array, or a list, of 1-99. This means that we plan to use the MID function 99 times, each time starting at a different position from 1 to 99.

Before:

=SUM(MID("0abf5fb6",LARGE(INDEX(ISNUMBER(--MID("abf5fb6",ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)

After:

=SUM(MID("0abf5fb6",LARGE(INDEX(ISNUMBER(--MID("abf5fb6",{1;2;3;...;99},1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)

(To save room, I didn't type out all of the numbers from 1-99, but I'm sure you get the point.)

Now that we have all the pieces to the second MID function, we can calculate this part out.

Before:

=SUM(MID("0abf5fb6",LARGE(INDEX(ISNUMBER(--MID("abf5fb6",{1;2;3;...;99},1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)

After:

=SUM(MID("0abf5fb6",LARGE(INDEX(ISNUMBER(--{"a";"b";"f";"5";"f";"b";"6";"";"";"";...;""})*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)

So what just happened here? Well the MID function just returns a substring of the string given, starting at the given location, for the specified number of characters. So we gave it the string, abf5fb6, gave it an array of starting positions, specifying we only want to extract 1 character. Thus, the function returns to us an array of each character in our string, from 1-99. Since our starting string was only 7 characters long, then the positions 8-99 are just empty, thus why we have all of the empty substrings after, ("";"";"";...;"";).

The next function to calculate is the ISNUMBER function, but first let's go over an odd feature we do first. Notice how there is a double minus in front our new array. While a single minus sign will reverse the result of an outcome (if TRUE then return FALSE and visa-vera), but a double minus means to force the string answer into a number. So usually this will result in the answers of TRUE to turn into a 1 and a FALSE to turn into a 0, but in this case, we are converting each character in our array to be a number. So entering in --"a" will result in #VALUE! while entering in --"5" will result in 5.

Thus, when we execute the ISNUMBER function:

=SUM(MID("0abf5fb6",LARGE(INDEX(ISNUMBER(--{"a"; "b"; "f"; "5"; "f"; "b"; "6"; ""; ""; ""; ...; ""})*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)

What we are actually executing behind the scenes is:

=SUM(MID("0abf5fb6",LARGE(INDEX(ISNUMBER({#VALUE!; #VALUE!; #VALUE!; 5; #VALUE!; #VALUE!; 6; #VALUE!; #VALUE!; #VALUE!; ...; #VALUE!})*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)

As a result, it turns an array of TRUE or FALSE indicating if the value was a number or not. Hence we get:

=SUM(MID("0abf5fb6",LARGE(INDEX({FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; ...; FALSE})*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)

Ran out of time Well I'm at work doing what I'm not supposed to be doing, and this was as far as I got before I need to actually do something productive today. Hopefully I'll pick back up where I left off later on.

1
  • This is incredibly through and very well written. Thank you very much, can't wait to see the rest of the answer! I've already learned so much
    – wizlog
    Jul 26, 2017 at 17:46
1

enter image description here

We can "unroll" this formula

=SUM(
   MID(
     0&A2,
     LARGE(
       INDEX(
         ISNUMBER(
           --
           MID(
               A2,
               ROW($1:$99),
               1
           )
         )*ROW($1:$99)
         ,
       ),
       ROW($1:$99)
     )+1,
     1
   )*10^ROW($1:$99)/10
 )

and follow by all the transformations of the operand(s).

First of all, the expression ROW($1:$99) represents an array of increasing natural numbers {1,2,...,99}. It is often used as a convenient block to build an array formula.

Next, A2 here is an address of the input cell, which contains a text string with mixed numbers, for example, R824TX01rQ768.

So, construction

MID(
  A2,
  ROW($1:$99),
  1
)

means: create an array of all symbols (strings of length 1, according to the third parameter in the MID(...,1)) from the input A2 <1>.

The elements which index is greater than the length of the string in A2 are empty strings.

This array of symbols is then prefixed with double minus --, which will turn a numeric symbol into a corresponding number and other symbols into the error value #VALUE! <2>.

Next, a function ISNUMBER() operates on that array of mixed chars/numbers and result in an array of boolean true/false values <3>,

which is multiplied element-wise by a familiar ROW($1:$99). When a true/false value is multiplied by the number, true is interpreted as 1, and false as 0, thus the result is a numeric array, which for every character position in A2 contains either 0, if the symbol is not a digit, or the index, if the character is digit <4>.

Function

INDEX(<the array>,)

or, expanded,

=INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),)

takes this numeric array and an empty second argument, which basically results in the same array <5>.

Expression

LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))

sorts our array operand obtained from INDEX(...) in descending order <6>

Expression

MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)

first prefixes the string in A2 with 0 and extracts the symbols in the sorted order, starting with the last digit found <7>.

And every digit in the obtained array is multiplied by 10^ROW($1:$99)/10 <8>.

MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10

<9>

Finally,

=SUM(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10) 

sums all the numbers in the array, which results in a desired number <10>.

Not sure, why the repeating construction of INDEX(<array>,) is used, it seems that the formula works without it as well:

=SUM(MID(0&A2,LARGE(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)

<11>

Perhaps, some compatibility issues?

4
  • First of all, thank you for writing such a long answer. I look forward to reading it in its entierity. In one of your first few lines though, you wrote <1>, what do the "<>" mean?
    – wizlog
    Jul 26, 2017 at 20:57
  • @wizlog : The numbers like <1> just mean a reference to the corresponding column in the attached screenshot, which displays the result of the step discussed.
    – g.kov
    Jul 26, 2017 at 21:06
  • This formula is crazy. However, can it be improved slightly? I think strings are limited to 256 characters, so perhaps $1:$99 Should be $1:$256? Will this hit a value limit?
    – Yorik
    Jul 26, 2017 at 21:38
  • 1
    @Yorik : Actually, text strings can be much longer than 256, check out Excel-specifications-and-limits. The real limit for this kind of formula would be the number of digits in the input text, because of the numeric accuracy limitations.
    – g.kov
    Jul 26, 2017 at 22:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .