Excel 2024: Text Before or After a Specific Delimiter

October 15, 2024 - by Bill Jelen

The new TEXTBEFORE and TEXTAFTER functions debuted on March 16, 2022 for Microsoft 365 Insiders. These functions isolate the text before or after a specific delimiter.

The function syntax requires some text and the delimiter. Optional arguments: instance_num for which delimiter, match_mode for case sensitive, match_end prevents an #N/A if the delimiter is not found. TEXTBEFORE("Cher"," ") returns #N/A. =TEXTBEFORE("Cher"," ",,,1) returns Cher.

More Reading

Excel 2024: Find Largest Value That Meets One or More Criteria »

October 11, 2024 - by Bill Jelen

One of the new Office 365 functions added in February 2016 is the MAXIFS function. This function, which is similar to SUMIFS, finds the largest value that meets one or more criteria: You can either hard-code the criterion as in row 7 below or point to cells as in row 9. A similar MINIFS function finds the smallest value that meets one or more criteria.

Excel 2024: New LAMBDA Helper Functions »

October 9, 2024 - by Bill Jelen

About a year after LAMBDA was introduced, Microsoft realized that we needed some helper functions. They gave us MAP, REDUCE, SCAN, MAKEARRAY, BYROW, BYCOL, and ISOMITTED.

Excel 2024: Combining Subformulas into a Single Lambda »

October 7, 2024 - by Bill Jelen

Often, I will need a complicated formula. It is easier to build that formula in tiny steps. Once the subformulas are working, you can try to combine everything into one complicated formula.

The Transformative Magic of Power Query M Code in Excel and Power BI

August 2024

A beginner’s guide to mastering the art of data metamorphosis to get just the data structure needed to create insightful data analysis solution

MrExcel 2021 - Unmasking Excel

February 2021

This is a 5th edition of MrExcel XL. Updates for 2021 include: LAMBDA, LET, Power Query Fuzzy Match, Sort & Filter in Sheet View, Cut-out people, Save object as image, STOCKHISTORY, Wolfram Alpha Data Types, Custom Data Types from Power Query, Weather data types, bilingual spreadsheets, Performance improvements, Unhide multiple worksheets, Action pen, Collapsible task panes, LET function to re-use calculations, store formulas using LAMBDA, Recursive LAMBDA, Branching LAMBDA, Lambda to return a picture, Excel function quick reference.

MrExcel 2024 Igniting Excel

February 2024

This is the 7th edition of MrExcel XL. Updates for 2024 include: Remove Filter Items Using Search Box, The Consolas Font Makes it Easy to Tell Zero from the Letter O, Display Online Pictures In a Cell Using a Formula, Place Pictures In Cell From Local Computer, Using Artificial Intelligence with Copilot for Excel, and more...

Guerrilla Data Analysis Using Microsoft Excel - 3rd Edition

May 2022

Two of the leading Excel channels on YouTube join forces to combat bad data. This book includes step-by-step examples and case studies that teach users the many power tricks for analyzing data in Excel. These are tips honed by Bill Jelen, "MrExcel," and Oz do Soleil during their careers run as financial analysts.

see more products »

Excel 2024: Text Before or After a Specific Delimiter

More Reading

Featured Products