In Power Query, load your statement as a table, select the narration column, then choose Split Column and By Delimiter. Enter the slash, decide whether to split at the left-most, right-most or every delimiter, and set the data types. Because Power Query records each step, next month you paste the new rows into the source and click Refresh, and the same clean-up runs again.
How do you load the data?
Power Query is built into Excel 2016 and later and into Microsoft 365, under the Data tab as Get and Transform. Menu names shift a little between versions, so check yours.
- Click inside your statement range, then choose From Table/Range on the Data tab. Tick My table has headers.
- The Power Query editor opens with your data. The original sheet is untouched.
- Click the header of the narration column to select it.
How do you split at the first, last or every delimiter?
Choose Split Column, then By Delimiter, and a dialog opens. Pick Custom, type a slash, and choose one of three options under Split at:
| Option | Effect on UPI/412345678901/SHARMA TEA STALL/sharmatea@okbank |
|---|---|
| Left-most delimiter | Two columns: UPI and the rest |
| Right-most delimiter | Two columns: everything up to the last slash, and sharmatea@okbank |
| Each occurrence of the delimiter | Four columns, one per segment |
The invented narration above is only an illustration, so use your own layout. The left-most and right-most options are handy when you only need a prefix or the final segment, such as the handle. Under Advanced options you can also split into Rows instead of Columns, which turns one narration into several rows. That is rarely what you want for a statement, because it duplicates the amount on each row.
What should you do after splitting?
- Rename the new columns. They arrive as Narration.1, Narration.2 and so on, which means nothing in a pivot.
- Set the type for each one. Choose Text for the reference column, so that leading zeros and long numbers are kept as written. Excel may add a Changed Type step on its own that converts the reference to a number, so check it and change it back if so.
- Trim the text. Select the columns, choose Transform, then Format, then Trim, to remove stray spaces.
- Remove the columns you do not need, such as the mode segment.
Finish with Close and Load, and the cleaned table appears on a new sheet.
Will next month's file clean itself?
Mostly, yes. The steps you applied are stored in the query, shown in the Applied Steps pane. Paste next month's rows into the original source table, with the same column headings, then use Data, then Refresh All. The split runs again on all rows.
There are two catches to know about. First, the split step is written for the number of columns found when you built it. If a later narration has more segments than the largest one you saw, the extra segments may not appear, so check the right-hand end of your output. Second, if the bank renames a column heading, the step that refers to it fails, and you will see an error until you fix the name.
What if some rows split differently?
Rows with fewer delimiters leave empty cells, and rows with more may lose data or run into the final column. To spot trouble, add a helper before the split. In the editor, choose Add Column, Custom Column, and use the formula Text.Length([Narration]) - Text.Length(Text.Replace([Narration], "/", "")). It counts the slashes. Filter the count column to see which shapes exist, then deal with the odd ones separately.
A routine you can repeat
- Keep one source sheet with a table called something like Statement. Paste each month below the last.
- Refresh the query on a separate output sheet.
- Filter the output for blanks in the merchant column and read those rows.
- Compare the total of the amount column with the statement total.
If your aim is a categorised view of spending, SpendixAI can take the statement as PDF, CSV or Excel and show each row for review before you save it, which skips the query-building step. Power Query is the better choice when you want full control of the sheet.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

