Excel Formula to Find Second To Last Word In Excel - Episode 2478

Microsoft Excel Tutorial: Formula to find second to last word in Excel.

Welcome to episode 2478 of MrExcel's netcast, where we dive into the world of Excel formulas and functions. In today's episode, we will be tackling a common problem faced by many Excel users - how to find the second to last word in a cell. Don't worry, we've got you covered with a simple and efficient solution.

In our previous episode, we used the TEXTAFTER function with a -1 argument to get the last word in a cell. However, when we try to use -2, we run into a problem. The spaces in the cell are being counted as individual words, throwing off our results. Unlike the TEXTSPLIT function, there is no option to ignore duplicates in TEXTAFTER. So, what's the workaround?

Well, the solution lies in combining the TRIM and TEXTAFTER functions. By using TRIM on the cell first, we eliminate any extra spaces and then feed the result into TEXTAFTER. This gives us the last two words in the cell. But we're not done yet. We then use the TEXTBEFORE function on the result of TEXTAFTER to get everything before the space, giving us the second to last word. It may sound a bit confusing, but trust me, it's much simpler than our previous episode that took a whopping 484 seconds to solve.

But wait, let's not make it too easy. For those of you who love a good challenge, I have an alternative solution for you. In this method, we use the LET function to create an array of all the words in the cell, and then use the INDEX function to retrieve the second to last word. This may make your head spin a bit, but hey, where's the fun in Excel if it's not a little bit complicated, right?

So there you have it, two ways to find the second to last word in an Excel cell. I hope you found this episode helpful and learned something new. Don't forget to subscribe to our channel for more Excel tips and tricks. Until next time, this is Bill Jelen signing off. Happy Excel-ing!

Table of Contents
(0:00) Problem Statement: Find second to last word in a cell
(0:12) Removing double spaces using TRIM function in Excel
(0:35) Using TEXTBEFORE and TEXTAFTER functions
(0:48) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelformula #excelformulasandfunctions #microsoft365

This video answers these common search terms:
Counting words in an array
Episode 2326 and its duration
Find the 2nd to last word in a cell
Getting everything before a space using TEXTBEFORE function
Issue with spaces being seen as two words
Making Excel more complicated
TEXTAFTER function in Excel
Using -2 in TEXTAFTER function
Using INDEX function with an array
Using LET function in Excel
Using new functions in Excel
Using TRIM and TEXTAFTER functions together

Thanks to the new TEXTAFTER function, this is now much easier than the old method in episode 2326.

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.

[Bforzajuve]

So we can potentially be 20 times more efficient with the new functions. That's great. Thank you very much Mr. Bill.

[wayneedmondson]

The new functions keep on giving! Thumbs up!!

[hellboy]

Is their any way in Excel to from parent and child and then grandchild (the issue is as you move child to grandchild the numbers increase and I can't do a next level lookup) ? Like we can do for child to parent?

[hawler-vdwh]

Hi, sir, I have a question if you help me. Thank you. When instal office 2021 falls without any new benefits, it will be 2019. Thank you for your help.

[Ali_]

Hi MrExcel. In fact, I have a question that I did not find an answer to. I deleted the excel sheet by mistake and saved the file and closed it. Can I get that paper back?

[DimEarly]

If your next 20 videos really are this, I am here for it! I have a 50-minute that I think would now be about 15...
I did this one with =CHOOSECOLS(TEXTSPLIT(TRIM(A1), " "), -2)