Spreadsheets

TEXTBEFORE, TEXTAFTER and TEXTSPLIT for narrations in Excel 365

Use TEXTBEFORE, TEXTAFTER and TEXTSPLIT in Excel 365 to cut bank narrations apart by delimiter, with nth-instance, if_not_found and spill examples.

Editorial illustration for TEXTBEFORE, TEXTAFTER and TEXTSPLIT for narrations in Excel 365

In Excel 365, TEXTAFTER takes the text after a delimiter, TEXTBEFORE takes the text before it, and TEXTSPLIT breaks the whole narration into columns. They replace long FIND and MID chains with one readable formula. They are not in older Excel such as 2016 or 2019, and you should check your own version before you build a sheet other people will open.

Which Excel versions have these functions?

TEXTBEFORE, TEXTAFTER and TEXTSPLIT came to Microsoft 365 and Excel for the web. Do not assume Excel 2021 or earlier has them. Check by typing =TEXTBEFORE( in a cell: if Excel does not offer the function, your version lacks it, and the FIND and MID method is the one to use. Google Sheets has no function by these names, but SPLIT does a similar job.

What do the arguments mean?

  • TEXTBEFORE(text, delimiter, instance_num, match_mode, match_end, if_not_found)
  • TEXTAFTER(text, delimiter, instance_num, match_mode, match_end, if_not_found)
  • instance_num says which occurrence of the delimiter to use. A negative number counts from the end, so -1 is the last one.
  • match_mode 0 is case-sensitive and 1 ignores case.
  • match_end 1 treats the end of the text as a delimiter.
  • if_not_found is what to show when the delimiter is missing, instead of #N/A.

How do you get the merchant from an invented UPI narration?

Take the narration UPI/412345678901/SHARMA TEA STALL/sharmatea@okbank in A2. The name is the third segment, after the second slash and before the third.

GoalFormulaResult
Text after the 2nd slash=TEXTAFTER(A2,"/",2)SHARMA TEA STALL/sharmatea@okbank
Merchant only=TEXTBEFORE(TEXTAFTER(A2,"/",2),"/")SHARMA TEA STALL
Last segment=TEXTAFTER(A2,"/",-1)sharmatea@okbank
First segment=TEXTBEFORE(A2,"/")UPI

Read the merchant formula from the inside out: TEXTAFTER chops off everything up to the second slash, and TEXTBEFORE then keeps the text up to the next slash. If a narration may have only two segments, the inner part would give #N/A, so add a fallback:

  • =TEXTBEFORE(TEXTAFTER(A2,"/",2,,,"CHECK"),"/",1,0,1)

The empty arguments are skipped by commas. The 1 at the end treats the end of the text as a delimiter, so a narration with no slash after the name still returns the name.

How do you split a whole narration across columns?

TEXTSPLIT takes the text and a column delimiter, then spills the pieces to the right:

  • =TEXTSPLIT(A2,"/") puts each segment in its own cell, starting where you typed it.
  • =TEXTSPLIT(A2,"/",,TRUE) ignores empty segments left by double slashes.
  • =TEXTSPLIT(A2,"/",,FALSE,,"-") fills short rows with a dash instead of #N/A. The fourth argument is ignore_empty and the sixth is pad_with.

The cells to the right of the formula must be empty, or Excel shows #SPILL!. Clear them, or move the formula to a free area. One more point: TEXTSPLIT works on one text value at a time, so fill the formula down the rows rather than pointing it at a whole column.

What if the delimiter is not what you expect?

Some narrations use a hyphen, a space, or an asterisk. Pick the delimiter from your own file, not from an example here. If a name itself contains the delimiter, the count of segments changes, and nth-instance formulas will cut at the wrong place. Test for it with =LEN(A2)-LEN(SUBSTITUTE(A2,"/","")), which returns the number of slashes.

Before you rely on it

  1. Confirm every user of the file has Microsoft 365.
  2. Run the slash count to see whether segments are consistent.
  3. Spot-check ten rows against the original narration.
  4. Copy the cleaned column and paste as values if the file will be shared with older Excel.

If cleaning narrations is a monthly chore, SpendixAI accepts a bank or card statement as PDF, CSV or Excel and lets you review rows before saving. For a sheet you control, the three functions above are tidy and quick.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports