Excel: Finding Text After a Delimiter

  The 14 new functions are currently available to Microsoft 365 customers who opt into the beta channel of Office Insiders. Eventually, the functions will make their way to Microsoft 365 customers who are on the monthly and semiannual update channels. They are TEXTSPLIT, TEXTBEFORE, TEXTAFTER, VSTACK, HSTACK, CHOOSECOLS, CHOOSEROWS, DROP, TAKE, EXPAND, TOCOL, TOROW, WRAPCOLS, and WRAPROWS.  

USING TEXTBEFORE AND TEXTAFTER

  In the first few weeks of using these functions, I found that one intriguing variation of TEXTAFTER allows you to search the contents of a cell starting from the end, allowing you to isolate the last word in a phrase or the last segment of a cost center. But first, let’s take a look at the vanilla use of TEXTBEFORE and TEXTAFTER.  

Column A of Figure 1 contains part numbers. Each includes a prefix, a dash, and a suffix. To isolate either the information before or after the dash, previously you’d need to use a formula that involved a combination of MID, LEFT, and FIND. With the new TEXTBEFORE function, however, you can get everything before the first dash with =TEXTBEFORE(A5,"-"). And to get everything after the dash, use =TEXTAFTER(A5,"-"). These are nice improvements to the old combination. (See rows 16 and 17 of Figure 1 for the old formulas.)  

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

The text argument identifies the text that will be searched for the delimiter. The delimiter isn’t limited to a single character such as a dash (-), period (.), or a particular letter. It can be longer, such as “cost center: ” or some other phrase.  

The instance_num lets you search for a particular occurrence of a delimiter. The match_mode argument lets you specify if the delimiter is case-sensitive. And the match_end argument prevents an error if the delimiter isn’t found. For example, =TEXTBEFORE("John Smith"," ") would return John. That formula fails, however, when there’s no space in the cell. To illustrate, =TEXTBEFORE("Cher"," ") returns an #N/A error since a space isn’t found in the text. If you specify 1 as the match_end argument, then the end of the text is considered to be a match for the delimiter, e.g., =TEXTBEFORE("Cher"," ",,,1) prevents the #N/A error and returns Cher. The if_not_found argument allows you to specify an alternate formula or text to display if the delimiter isn’t found.  

THE INSTANCE_NUM ARGUMENT

  What if your data has multiple delimiters of the same kind? For instance, if you need to find everything after the third dash, you could specify an instance_num of 3: =TEXTAFTER("123-456-789-ABC","-",3) would return a value of ABC.  

But what if you don’t know how many delimiters will be in a cell? To have a formula isolate everything after the last delimiter, the previous formula was a confusing combination of MID, FIND, LEN, SUBSTITUTE, LEN, and SUBSTITUTE all wrapped in a LET function. The convoluted formula in cell A14 of Figure 2 finds how many delimiters are in a cell by removing all delimiters and then comparing the length of the result with the length of the original text. You’ll never have to resort to this complicated formula thanks to an invention by Excel MVP Charles Williams.  

In his Fast Excel utility, Williams introduced the concept of allowing arguments such as instance_num to count from the left or right edge of the cell. Specify a positive number, and Excel counts from the left. Use a negative number as the instance_num, however, and Excel will start the search from the end of the cell.  

Thus, to find the last occurrence of a delimiter, you would specify -1 as the instance_num argument.  

Thankfully, the Excel team followed this convention for the instance_num in TEXTAFTER and TEXTBEFORE. In Figure 2, the formula to find the last word in a phrase is =TEXTAFTER(A4," ",-1). This formula will find everything after the last space in a cell. This formula is dramatically simpler than the previous formula shown in cell A14.  

Many of these 14 new functions help simplify formulas. If you regularly face this type of problem, it’s easy to join the Office Insiders program online to get early access to the new functions. Note that there are two levels of Office Insiders. You want to select the beta option.