How to create a mega formula -1/2

Excel formulas are beautiful and I am obsessed with string manipulation and lookup reference formulas, individually all formulas do very simple thing but when we learn how to combine them, we can have them do whatever we want to do like the question below.

We have a WhatsApp group to discuss Excel VBA stuff, you can share your query or share any solution. Click here if you want to get added in our amazing (spam free) group. so this query came there yesterday and I was able to create a solution by formula. so have a look.

So, you might have noticed that we need to extract all numbers from the input text, and the extracted numbers should come in ascending order too. Woaahh.. looks pretty easy to me at first look. but it wasn’t indeed.

Learn the hack of the mega formula. Join weekoff’s special formula session with us.

As this post name is “how to create mega formula” I will talk about that. Mega formulas are combination of many other functions which are returning values to another function which is returning values to other function (and most of the time it is an array formula). Sounds confusing? lets see –

I am assuming that if you are reading this post you already knows how MID formula works. but if you don’t know, don’t worry, I will tell you right now. MID() is a text formula, which return a substring from a string by given starting point to given length. As an example –

if you have a text “Hello, How are you?” and you apply =Mid(YourText,8,3), this mid formula will return “How”. If you apply =Mid(YourText, 1,1)  it will return “H“, easy?

So now, if I say get all the characters of this string split individually in separate cells? you would probably use MID() formula in separate cells like below.


All these formulas looks pretty same, all are using same range reference and same character length (last parameter of mid function) , but the only parameter that is getting change is “start_number” (Second parameter of mid formula). which is 1,2,3,4,5,6,7,8,…. so on. What if I tell you that you can make this parameter increasing dynamically and automatically based on the text length and you don’t need put formulas in each cells and go and change “start_number” manually? that would be a fun, wouldn’t it be?. lets see how we can do that.

Here array comes into play. An array is a collection of values. in Excel formulas array starts and end with a curly bracket “{}”, whatever written in between these brackets are array elements. An array will looks like {1;2;3;4;5;6;7;8}. So back to you mid formula in above image. only parameter which is changing is “start_number” what if we make that parameter an Array and just use mid formula only in one cell and still break all the characters? lets try that..

(To calculate any formula result hit F2,select the formulas or sub-formula and hit F9)

=Mid(YourText,{1;2;3;4;5;6;7;8},1) will return ={“H”;”e”;”l”;”l”;”o”;”,”;” “;”H”}

Now we have broke first 8 characters in our formula, but this is not what we need, we need to break all characters and we need a formula which can return an array like above.

Now it is time to learn ROW() function. Row() function is very basic function which return the row number of the current cell ( in which row the formula is applied) or if we pass any reference, it will return the row number of that reference. pretty simple. let see an example

=Row() = Current Row number

=Row(A1) = 1

= Row(A2)=2

=Row(A1:A2)= {1;2} — it is returning an array here.

So now you know if we pass multiple cells reference, Row() function will return an array. so for our MID function example if we do =Mid(YourText,Row(A1:A8),1) this will return ={“H”;”e”;”l”;”l”;”o”;”,”;” “;”H”}. now the only thing we need to do is to make this reference dynamic so that it can have number of rows equal to the length of our text. well, we can create a string like “=A1:A” & Len(YourText) this will return A1:A19.

This looks perfect now if we do Row(“A1:A” & Len(YourText)). it should return an array of 1 to 19 like {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}.  But unfortunately it won’t do that, I told you this because when you do any formula you need to pay attention to the function’s parameter data type. when you create a dynamic string by concatenating Len(), it actually return a Text/String, but Row() function accept only Reference. so first of all we need to convert this dynamic string into a reference prior using it in Row function.

Ok, so which formula will do that? well, we have INDIRECT(), this is born to convert a reference string to an actual reference. so we will use =Indirect(“A1:A” & Len(YourText)), and this will be treated as an actual reference. now you can use =Row(Indirect(“A1:A” & Len(YourText))), perfect we got our dynamic array to use in Mid “start_number” parameter.

finally, we will use =Mid(YourText, Row(Indirect(“A1:A” & Len(YourText))), 1) and it will split each character as an element in your Array.

={“H”;”e”;”l”;”l”;”o”;”,”;” “;”H”;”o”;”w”;” “;”a”;”r”;”e”;” “;”y”;”o”;”u”;”?”}

Congrats, you have learn the most complicated trick of array formulas. now lets go back to our formula challenge. and we will see how we will use this trick to solve that challenge BUT… in my next post.

3 replies
  1. various
    various says:

    Hellߋ very nice web site!! Man .. Beautіful .. Wonderful ..
    I will bookmark your blog and take the feeds аɗditiοnalⅼy?
    I am ցlаd to seek out so many uѕeful info here withіn the publish, we’d like work out extra
    tecһniques in this regard, thanks for sharing. . . . . .

    Reply

Trackbacks & Pingbacks

  1. […] 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. […]

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *