The next step

The Office Insiders program

[Weblink]

Using Excel’s new TEXTAFTER function

TEXTBEFORE and TEXTAFTER

Microsoft released a number of new beta functions in the summer of 2022 which are available to anyone signed up to the Office Insider program (which is free). Two of these were TEXTBEFORE and TEXTAFTER.

How do they work? Let’s say that you had a list of customer names in one column in Excel in the format “Surname, First name”. To isolate either the information before or after the comma, previously you’d need to use a formula that involved a combination of MID, LEFT, and FIND. With the new functions, to extract the surname from a name in cell A2 into a separate column, you can now use =TEXTBEFORE(A2,”,“). Similarly, you could use the TEXTAFTER function to extract the first name into a separate column: =TEXTAFTER(A2,”,”).

Syntax. The Excel team designed both TEXTBEFORE and TEXTAFTER to allow several optional arguments. The complete function syntax is =TEXTAFTER(text, delimiter, instance_num, match_mode, match_end, if_not_found).

Tip. The delimiter isn’t limited to a single character such as a dash, full stop or a particular letter. It can also be longer, such as “cost centre: ” or some other phrase. Tip. The text can be in the form of a text string or a cell reference. Tip. If the delimiter is not found, the function can return the #N/A error, your own text or the original string.

Multiple delimiters

What if your data has multiple delimiters of the same kind? For instance, if you need to find everything after the third dash in a cell, you could specify an instance_num of 3: =TEXTAFTER(“A2”,”-“,3). Tip.  To find the last occurrence of a delimiter, you can specify -1 as the instance_num argument.

Example. You have a cell with customer addresses in the format: Number and Street, Town, County and you just want the county. You can use the formula =TEXTAFTER(A2,”,”-1) and it will find everything after the last comma in a cell.

Match end of text

Normally TEXTBEFORE doesn’t treat the end of a text string as a delimiter. For example, by default the formula below will return #N/A because there is no fourth delimiter:

=TEXTBEFORE(“ABX-123-Red-XYZ”,”-”,4)

However, if you enable match_end by providing 1, the formula behaves as if a delimiter exists after XYZ:

=TEXTBEFORE(“ABX-123-Red-XYZ”,”-”,4,,1) returns XYZ.

Tip. Watch out for situations where a delimiter can’t be found. If match_end is enabled and instance_num is 1, TEXTBEFORE will return the entire text string if the delimiter is not found.

Case sensitivity

By default, TEXTBEFORE and TEXTAFTER are case-sensitive when searching for delimiter. This behaviour is controlled by the match_mode argument which enables and disables case-sensitivity. By default, match_mode is 0 which means it is case-sensitive. Tip. To disable case-sensitivity, use 1 for the match_mode in the formula.

For the Office Insiders program and a TEXTAFTER spreadsheet example, visit https://www.tips-and-advice.co.uk , Download Zone, year 14, issue 11.