MrExcel Excel Videos

You have a bunch of sentences in Excel. How do you find the last word in each cell using a formula? The new TEXTAFTER function solves the problem.

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.

Why does the new TEXTSPLIT function in Excel return #N/A errors at the right side? When does a double space create a blank cell? Solve both problems using optional arguments for Ignore_empty and Pad_With.

How to get the Unique list of items appearing in a rectangular range. This solution uses the new TOCOL function released on March 16, 2022 to Microsoft 365 Office Insiders.

Today, a deeper dive into the new Excel functions TOCOL and TOROW. Table of Contents (0:00) Ignore errors with TOCOL (0:46) Non-contiguous ranges with TOCOL (0:56) 3-D Ranges with TOCOL (1:26) Using TAKE and HSTACK (2:01) VSTACK for Headings

The Question: "How do I generate the next Invoice Number?" Microsoft Office Online offers a great number of Invoice Templates. However, none of those templates has a Macro to pull the next Invoice Number. Today, in Episode #1505, Bill shows us how to 'Macro Enable' our Workbook and then create a short VBA Code routine to automatically generate Invoice Numbers with the press of a mouse button. Note: A similar discussion happened in episodes 742-745. You might want to review those videos as well, as they go the extra step of creating an add-in. PLEASE - if you need to save as PDF or if you alphanumeric invoice numbers, read through the FAQ at Next Invoice Number. Your question has already been answered many times in the comments below...

Antony in Toronto brings us two more Excel AutoSum Oddities. This was part of my hour-long session on Excel Oddities, Curiousities and Mysterious Wonders for the Vancouver Modern Excel Users Group. Watch the entire hour here:

Doug from Boston needs to calculate an age in Excel. Two easy formulas today: YEARFRAC and DATEDIF.

The icons for Rows & Columns in the Pivot Table Field List are backwards. They've been backwards for quite some time. Unless, it is actually a garden hose, spraying data across the top of the report… Thanks to Nicole in Appleton for that memory aid! Also in this Friday episode: Classic Pivot Table layout, Blank Cells no longer cause a Count instead of a Sum. To download this workbook: https://www.mrexcel.com/download-center/2018/11/the-excel-data-hose.xlsx List of upcoming seminars: Excel Seminar Schedule