Way back in episode 2326, I spent 8+ minutes showing how to use LET to get the second to last word from a cell. Today, it still requires TEXTBEFORE, TEXTAFTER, and TRIM but can be done in 45 seconds.
Transcript of the video:
Find the 2nd to last word in a cell.
Alright, so before we used the TEXTAFTER after function to get the -1.
If we use -2, that's going to give us the last two words.
But we're having a problem here because the space spaces are being seen as two words.
And unlike TEXTSPLIT, there is no option here to ignore duplicates.
So what do we have to do?
Well, I'm going to use the TRIM of A2 and then send that into TEXTAFTER. That gets me two words.
And then finally the TEXTBEFORE of the TEXTAFTER.
And this time we want everything before the space that gets us the 2nd to the last word.
A little confusing, but not as confusing as episode 2326 that took 484 seconds. Well I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
I’ve had it – these new functions make Excel too easy.
There's nothing geeky about that.
Let's at least make it a little bit more complicated.
Here – LET A equal the TEXTSPLIT. That gets us all the words into an array called A.
Count how many words there are in a store that in B minus one.
And then the INDEX of all those words comma B. At least, this makes your head spin a tiny bit.
Alright, so before we used the TEXTAFTER after function to get the -1.
If we use -2, that's going to give us the last two words.
But we're having a problem here because the space spaces are being seen as two words.
And unlike TEXTSPLIT, there is no option here to ignore duplicates.
So what do we have to do?
Well, I'm going to use the TRIM of A2 and then send that into TEXTAFTER. That gets me two words.
And then finally the TEXTBEFORE of the TEXTAFTER.
And this time we want everything before the space that gets us the 2nd to the last word.
A little confusing, but not as confusing as episode 2326 that took 484 seconds. Well I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
I’ve had it – these new functions make Excel too easy.
There's nothing geeky about that.
Let's at least make it a little bit more complicated.
Here – LET A equal the TEXTSPLIT. That gets us all the words into an array called A.
Count how many words there are in a store that in B minus one.
And then the INDEX of all those words comma B. At least, this makes your head spin a tiny bit.
