How to create Excel Mega Formula 2/2
Welcome to the tutorial of creating mega formula. Assuming if you are reading this post you might already have read the first part of it, but if not, have a look here.
In my last post I tried to explain how to create array by using Row() an Indirect() function and then use that array into Mid() function to split up all characters in a string. if you need to do more practice, do it before you read further because now we are going solve the challenge explained in previous post.
Ok back the challenge. this is our image
From the input text we need to extract all numbers sorted ascending. let me show you the formula which will do that.
=IFERROR(Transpose(SMALL(IFERROR(VALUE(IFERROR(IF(MATCH(ROW(INDIRECT(“1:”&LEN(A1))),IFERROR(SMALL(IF(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)=”“,ROW(INDIRECT(“1:”&LEN(A1))),””),ROW(INDIRECT(“1:”&LEN(A1)))),””)ROW(INDIRECT(“1:”&LEN(A1)))+1,0),”“,””),””)&MID(SUBSTITUTE(A1,”“,””),ROW(INDIRECT(“1:”&LEN(SUBSTITUTE(A1,”“,””)))),1)),””),ROW())),””)
if you notice, that ROW(INDIRECT(“1:”&LEN(A1))) is repeating five times in the formula and really making it bigger and hard to understand, so let’ do one thing to make it shorter in terms of understanding. lets name this formula something.
row_array = ROW(INDIRECT(“1:”&LEN(A1)))
=IFERROR(Transpose(SMALL(IFERROR(VALUE(IFERROR(IF(MATCH(row_array,IFERROR(SMALL(IF(MID(A1,row_array,1)=”“,row_array,””),row_array),””)row_array+1,0),”“,””),””)&MID(SUBSTITUTE(A1,”“,””),ROW(INDIRECT(“1:”&LEN(SUBSTITUTE(A1,”“,””)))),1)),””),ROW())),””)
Alright, so it became shorter but is still hard to understand what it is doing.. let see any explanation of how individual function are working here.
So first of all we need to split up all the characters, so I have used that MID(ROW(INDIRECT(LEN))) technique from previous post. this will split each character and will give an array like {“S”;”1″;”u”;”r”;”2″;”v”;”D”;”4″;”v”;”r”;”5″;”n”;”8″;” “;”s”;”6″;”a”;”9″;”7″;”a”;”0″;”“;”1″;”“;”2″;”“;”3″;”“;”4″;”“;”5”}
now each characters are splited but left us with one more challenge, the negative ( ) sign is also coming as a separate element of the array, so it is very difficult to know which value Is negative and which is positive. but I figured out a way.
lets break this formula into two parts first part will return the negative sign if any. Second part will return the values and then we will contact both array element together. First part of the formula is this –
=IFERROR(IF(MATCH(row_array,IFERROR(SMALL(IF(MID(A1,row_array,1)=”“,row_array,””),row_array),””)row_array+1,0),”“,””),””)
in this formula we are checking if any splited element in array is equal to “” sign. and if it is a negative sign. we need to get the (position – 1;2;3}) of that negative sign, and the above formula will return this array
{“”;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;““;”“;”“;”“;” “;””;””;””;””;””}
Now we need second part so that we can concat with this array and this one is actually easy.
MID(SUBSTITUTE(A1,”“,””),ROW(INDIRECT(“1:”&LEN(SUBSTITUTE(A1,”“,””)))),1)
we removed all the negative sign from string by using SUBSTITUTE formula and then split the characters. which gave us an array like this.
{“S”;”1″;”u”;”r”;”2″;”v”;”D”;”4″;”v”;”r”;”5″;”n”;”8″;” “;”s”;”6″;”a”;”9″;”7″;”a”;”0″;”1″;”2″;”3″;”4″;”5″}
Now we have both part ready and if we concat them it will look like this.
{“S”;”1″;”u”;”r”;”2″;”v”;”D”;”4″;”v”;”r”;”5″;”n”;”8″;” “;”s”;”6″;”a”;”9″;”7″;”a”;”0″;”1″;”2″;”3″;”4″;”5″;#N/A;#N/A;#N/A;#N/A;#N/A}
Now use VALUE Function on this array, this will return error where the element are not number. so it will look like this
{#VALUE!;1;#VALUE!;#VALUE!;2;#VALUE!;#VALUE!;4;#VALUE!;#VALUE!;5;#VALUE!;8;#VALUE!;#VALUE!;6;#VALUE!;9;7;#VALUE!;0;1;2;3;4;5;#N/A;#N/A;#N/A;#N/A;#N/A}
Now, use IFERROR on this array to eliminate errors.
IFERROR(VALUE(IFERROR(IF(MATCH(row_array,IFERROR(SMALL(IF(MID(A1,row_array,1)=”“,row_array,””),row_array),””)row_array+1,0),”“,””),””)&MID(SUBSTITUTE(A1,”“,””),ROW(INDIRECT(“1:”&LEN(SUBSTITUTE(A1,”“,””)))),1)),””)
will Return
{“”;1;””;””;2;””;””;4;””;””;5;””;8;””;””;6;””;9;7;””;0;1;2;3;4;5;””;””;””;””;””}
Wow, we got our numbers and now we just need to sort them and as you know we use Small function to sort the numbers, we will use it here. so here is the complete formula you can use to get the output of the challenge.
=IFERROR(SMALL(IFERROR(VALUE(IFERROR(IF(MATCH(row_array,IFERROR(SMALL(IF(MID(A1,row_array,1)=”“,row_array,””),row_array),””)row_array+1,0),”“,””),””)&MID(SUBSTITUTE(A1,”“,””),ROW(INDIRECT(“1:”&LEN(SUBSTITUTE(A1,”“,””)))),1)),””),ROW()),””)
will Return
{5;4;3;2;1;0;1;2;4;5;6;7;8;9;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””}
Hope you understand what I mean by all this crap, it is really hard to hard explain by writing blog post. but to build you complex formulas you should know what each and individual formula is doing, and how to evaluate functions. you need to know what type of parameters a function will accept and which function can return that type of value.
Alright, see you next time. share your thoughts in comment.
‘
Subscribe to get news from IT Chat
Trackbacks & Pingbacks

[…] How to create mega formula 2/2 […]
Leave a Reply
Want to join the discussion?Feel free to contribute!
came up with short formrula later,
=SMALL(IFERROR(VALUE(IF(MID(” “&A1,ROW(INDIRECT(“1:”&LEN(A1))),1)=”“,”“,””)&IFERROR(VALUE(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)),””)),””),ROW(INDIRECT(“1:”&LEN(A1))))