Spreadsheets

Power Query: extract text between delimiters from a narration

Use Power Query's Extract Text Between Delimiters to pull a merchant name from a narration, with start and end index notes for repeated slashes.

Editorial illustration for Power Query: extract text between delimiters from a narration

In Power Query, select the narration column, then choose Transform, Extract, Text Between Delimiters. Enter the start and end delimiters, and under Advanced options tell it which occurrence to use. The step it writes is Text.BetweenDelimiters, which takes the text between two chosen delimiters, and you can edit its numbers when the same delimiter repeats.

How do you do it from the menu?

  1. Load the statement with From Table/Range on the Data tab.
  2. Select the narration column.
  3. On the Transform or Add Column tab, choose Extract, then Text Between Delimiters.
  4. Type a slash as the Start delimiter and a slash as the End delimiter.
  5. Open Advanced options and set Scan for end delimiter and the number of start delimiters to skip. Click OK.

Use Add Column, Extract if you want to keep the original narration, and Transform, Extract if you are happy to replace it. The first is safer.

What does the formula mean?

The editor writes a call that looks like Text.BetweenDelimiters([Narration], "/", "/", 1, 0). Its arguments are the text, the start delimiter, the end delimiter, then two optional numbers. The first number says how many start delimiters to skip, and the second says how many end delimiters to skip after that. Both count from zero, so 0 means the first one, and 1 means the second.

Using an invented narration, UPI/412345678901/SHARMA TEA STALL/sharmatea@okbank, a start index of 1 skips the first slash and begins after the second. An end index of 0 stops at the next slash after that point. The result is SHARMA TEA STALL. A start index of 0 would give the reference number instead.

Start indexEnd indexResult on the invented narration
00412345678901
10SHARMA TEA STALL

Test what happens on a row where the end delimiter is missing before you rely on the result, because that behaviour is worth confirming rather than assuming.

What if the delimiters differ at each end?

They need not match. To pull the handle from a name like sharmatea@okbank, use a different pair. Text.AfterDelimiter([Narration], "@") returns everything after the at sign. To read a value inside brackets, use "(" as the start and ")" as the end. The same function handles all of these.

How do you trim and handle failures?

Wrap the call in Text.Trim and add a fallback. In Add Column, Custom Column, enter:

  • try Text.Trim(Text.BetweenDelimiters([Narration], "/", "/", 1, 0)) otherwise "CHECK"

A narration that does not fit the pattern then shows CHECK, which you can filter on. Without the try, a row that behaves badly can turn into an error cell that stops a later step. Change the new column type to Text before loading.

Which index should you choose when the delimiter repeats?

Count the segments from the left. The first slash sits after segment one, so to read segment N you skip N minus 2 slashes and start after the next one. For segment two, the start index is 0 and the end index is 0. For segment three it is 1 and 0. If the part you want is always last, use Text.AfterDelimiter with the delimiter and a right-to-left option in its optional arguments, or split at the right-most delimiter from the Split Column menu. Check the function help in the editor for the exact form in your version.

A short checklist

  • Look at ten narrations and write down the segment you want.
  • Count slashes to confirm the shape is consistent.
  • Pick the start and end index, then compare three results with the original text.
  • Add the try and otherwise fallback, and filter for CHECK.
  • Check the total amount is the same before and after.

Once your merchant names are clean, a summary by merchant is a pivot away. SpendixAI also builds merchant-wise reports from imported statements, if you would rather not maintain the query yourself.

SPENDIXAI JOURNAL

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

Explore your reports