The Excel team introduced an amazing new LET function to Office 365 Insiders Fast on March 16, 2020. After the initial introduction video yesterday, today, I tackle a massive 340-character formula to find the second-to-last word in a phrase (aka the penultimate word).
And Yes, this can be done easily with VBA, but today I am taking a 300+ character formula from the MrExcel Message Board circa March 2010 and simplifying it dramatically.
And Yes, this can be done easily with VBA, but today I am taking a 300+ character formula from the MrExcel Message Board circa March 2010 and simplifying it dramatically.
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2326.
The LET function - reusing a variable in a later variable.
Hey, welcome back to MrExcel Netcast. I'm Bill Jelen.
My second episode here on LET() and this one is far more complicated than the first one and shows off the power of this.
My first episode yesterday we are at the MVP summit, but because of coronavirus everyone is doing it virtually.
They announced LET at 11 o'clock and I rushed to get a video out.
But then this morning, I went looking for a better example and I found this question from MrExcel message board.
Back in 2010, almost 10 years to the day.
Mad Max in asked how to find the 2nd-to-the-last word in the phrase and Special K99 came up with this formula and thanks to SpecialK 10 years later, for the basis of this solution, right?
So we have a bunch of phrases here to make life more difficult. There's spaces intermingled, right?
So the first thing I have to do is get rid of spaces. So the TRIM() of A4.
and this great function to figure out how many words we have. The length of A4 minus the length of.
Get rid of all the spaces in A4.
And, generally you have to add 1 to this, but then I had an extra column here that said "alright well which word do I want?" "I want the answer from this minus one".
"Which space do I want?" "I want the answer from that minus one", so that all simplifies down to that simple thing.
It's not really "which word" it is "which space", and then the awesome little trick here.
I'm going to SUBSTITUTE(), In this phrase, the space for a caret, but which space?
Oh, I want the fifth one, and depending on how many words we have, it will be a different number each time.
So what we're getting then is we're getting a caret, a caret, a caret, right before the word that I want.
Alright, so I call that Careted Text (CT) and then from there it's all fairly simple.
Get the MID() of this sub-answer, find the caret plus one and just grab 30 characters because, how long can a word be?
And then once we have those last two words - the last two words in the phrase - and it simply becomes find the space minus one all right.
But, you know, if Someone sends this question in, you can't give them five formulas like this, so I go through a process of condensing these down to one formula and I'm going to speed that up. I'll be right back.
Phew that is crazy right?
This formula is an insane formula of 370 characters.
And what's happening: the sub-answer in E4 was used twice.
D4 is used four times, C4 is four times B4 is used 12 times and so we're just repeating the same thing over and over and over again.
And that's where the LET() function is going to solve this much better, right?
So I come down to its we have four variables essentially that are reused. And boy I really should have called this "TrimmedPhrase".
but then the function becomes longer.
So I created this little logic steps out here with the variables I'm going to use.
Yes Jordan Goldmeier, look at me declaring variables here in the Excel grid instead of VBA.
Maybe I've reformed! So T is equal to the TRIM of A4, Which Word is the length of T? Beautiful. Look I get to reuse that.
minus the LENgth of the SUBSTITUTE of T where we get rid of the space, so that's counting which space, and then the SUBSTITUTE function of T. Changing a space to a caret.
Which one? The WW one. And then finding everything after the caret.
So the MID of the Careted Text, FIND a caret in CT plus one.
Comma 30 and then the last one is kind of just easy here.
Alright, let's walk through building of the LET function.
The syntax of LET: we start with Variable Name, comma, Definition. Variable name, comma, Definition.
Variable name and there can be as many of these as you want.
After that last comma, the final thing doesn't have a variable name, it's just the final calculation.
So this, right, and I'm going to build this rather than re type everything.
I have it all up there already. Alright.
So rather than re type all this, I'm going to use TEXTJOIN().
Ah, between each item I want a comma.
And we'll take pairs like this and then add a comma afterwards.
Copy that down.
The calculation is just going to be that value - perfect and then equal text join between each one of these.
I want to use CHARacter 10.
This whole thing. This will build the formula as text.
This will build the formula as text.
Alright and then an awesome trick.
I learned this in Cleveland at General Electric maybe 15 years ago. I have a formula there in K21.
I want to bring the value of that formula down to K22 so Control+ Shift+Quotation Mark. It's kind of like a "Ditto".
It brings it down and now I just press enter and that should allow that formula to calculate. "Latter". That's Great.
Alright so I have that working now. I will come back up here to our original data.
Paste and then copy and paste so "airport", "considered", "do". It looks like it's working.
All right, look at that: 154 characters.
Now here's the question.
I really had to build this mentally first or here in the grid first before I started building that LET function because otherwise I found that I was.
It was when I tried to just build the LET function without building this first.
It was super hard and then I almost wonder when I hand this off to a coworker. Ah.
You know, with just the original thing, and this is this, any easier to understand then yeah, I guess it is.
I guess there really should be some best practices here as far as like using the Alt+Enter to put each variable on a new line.
The problem is that people don't realize that they have to expand the Formula Bar. They think that that's the formula.
Uhm, you know and.
Like using T instead of TrimText.
Maybe I should call it TrimText, but I was trying to get it to be shorter.
Oh hey, let me interrupt. This is after the video.
I went back and changed T to TrimText and tried to make all these a little bit more readable.
Not caring about formula length now, just trying to make it easier to understand.
This is probably better! Back to the video. I don't know this is all pretty new.
It'll be really interesting to see what people come up with and how this develops.
This new LET function, but definitely definitely is an interesting interesting thing.
Alright, If you like these tips please Subscribe and "ring the bell". This book, MrExcel 2020.
I decided I'm actually going to print some of these, and while we've been selling the PDF, I am going to print.
The printer says I've got 16 extra pages so I'm going to add the LET function to this book.
If you've already bought the PDF, or if you buy the PDF today, I will get you the updated PDF as we go.
I want to thank the Calc team on the Excel team in Redmond for this awesome new LET function and I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
The LET function - reusing a variable in a later variable.
Hey, welcome back to MrExcel Netcast. I'm Bill Jelen.
My second episode here on LET() and this one is far more complicated than the first one and shows off the power of this.
My first episode yesterday we are at the MVP summit, but because of coronavirus everyone is doing it virtually.
They announced LET at 11 o'clock and I rushed to get a video out.
But then this morning, I went looking for a better example and I found this question from MrExcel message board.
Back in 2010, almost 10 years to the day.
Mad Max in asked how to find the 2nd-to-the-last word in the phrase and Special K99 came up with this formula and thanks to SpecialK 10 years later, for the basis of this solution, right?
So we have a bunch of phrases here to make life more difficult. There's spaces intermingled, right?
So the first thing I have to do is get rid of spaces. So the TRIM() of A4.
and this great function to figure out how many words we have. The length of A4 minus the length of.
Get rid of all the spaces in A4.
And, generally you have to add 1 to this, but then I had an extra column here that said "alright well which word do I want?" "I want the answer from this minus one".
"Which space do I want?" "I want the answer from that minus one", so that all simplifies down to that simple thing.
It's not really "which word" it is "which space", and then the awesome little trick here.
I'm going to SUBSTITUTE(), In this phrase, the space for a caret, but which space?
Oh, I want the fifth one, and depending on how many words we have, it will be a different number each time.
So what we're getting then is we're getting a caret, a caret, a caret, right before the word that I want.
Alright, so I call that Careted Text (CT) and then from there it's all fairly simple.
Get the MID() of this sub-answer, find the caret plus one and just grab 30 characters because, how long can a word be?
And then once we have those last two words - the last two words in the phrase - and it simply becomes find the space minus one all right.
But, you know, if Someone sends this question in, you can't give them five formulas like this, so I go through a process of condensing these down to one formula and I'm going to speed that up. I'll be right back.
Phew that is crazy right?
This formula is an insane formula of 370 characters.
And what's happening: the sub-answer in E4 was used twice.
D4 is used four times, C4 is four times B4 is used 12 times and so we're just repeating the same thing over and over and over again.
And that's where the LET() function is going to solve this much better, right?
So I come down to its we have four variables essentially that are reused. And boy I really should have called this "TrimmedPhrase".
but then the function becomes longer.
So I created this little logic steps out here with the variables I'm going to use.
Yes Jordan Goldmeier, look at me declaring variables here in the Excel grid instead of VBA.
Maybe I've reformed! So T is equal to the TRIM of A4, Which Word is the length of T? Beautiful. Look I get to reuse that.
minus the LENgth of the SUBSTITUTE of T where we get rid of the space, so that's counting which space, and then the SUBSTITUTE function of T. Changing a space to a caret.
Which one? The WW one. And then finding everything after the caret.
So the MID of the Careted Text, FIND a caret in CT plus one.
Comma 30 and then the last one is kind of just easy here.
Alright, let's walk through building of the LET function.
The syntax of LET: we start with Variable Name, comma, Definition. Variable name, comma, Definition.
Variable name and there can be as many of these as you want.
After that last comma, the final thing doesn't have a variable name, it's just the final calculation.
So this, right, and I'm going to build this rather than re type everything.
I have it all up there already. Alright.
So rather than re type all this, I'm going to use TEXTJOIN().
Ah, between each item I want a comma.
And we'll take pairs like this and then add a comma afterwards.
Copy that down.
The calculation is just going to be that value - perfect and then equal text join between each one of these.
I want to use CHARacter 10.
This whole thing. This will build the formula as text.
This will build the formula as text.
Alright and then an awesome trick.
I learned this in Cleveland at General Electric maybe 15 years ago. I have a formula there in K21.
I want to bring the value of that formula down to K22 so Control+ Shift+Quotation Mark. It's kind of like a "Ditto".
It brings it down and now I just press enter and that should allow that formula to calculate. "Latter". That's Great.
Alright so I have that working now. I will come back up here to our original data.
Paste and then copy and paste so "airport", "considered", "do". It looks like it's working.
All right, look at that: 154 characters.
Now here's the question.
I really had to build this mentally first or here in the grid first before I started building that LET function because otherwise I found that I was.
It was when I tried to just build the LET function without building this first.
It was super hard and then I almost wonder when I hand this off to a coworker. Ah.
You know, with just the original thing, and this is this, any easier to understand then yeah, I guess it is.
I guess there really should be some best practices here as far as like using the Alt+Enter to put each variable on a new line.
The problem is that people don't realize that they have to expand the Formula Bar. They think that that's the formula.
Uhm, you know and.
Like using T instead of TrimText.
Maybe I should call it TrimText, but I was trying to get it to be shorter.
Oh hey, let me interrupt. This is after the video.
I went back and changed T to TrimText and tried to make all these a little bit more readable.
Not caring about formula length now, just trying to make it easier to understand.
This is probably better! Back to the video. I don't know this is all pretty new.
It'll be really interesting to see what people come up with and how this develops.
This new LET function, but definitely definitely is an interesting interesting thing.
Alright, If you like these tips please Subscribe and "ring the bell". This book, MrExcel 2020.
I decided I'm actually going to print some of these, and while we've been selling the PDF, I am going to print.
The printer says I've got 16 extra pages so I'm going to add the LET function to this book.
If you've already bought the PDF, or if you buy the PDF today, I will get you the updated PDF as we go.
I want to thank the Calc team on the Excel team in Redmond for this awesome new LET function and I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
