Spreadsheets

Split a narration into columns by slash with Text to Columns

Split a bank narration into separate columns at each slash with Excel's Text to Columns, keep the original column safe and handle uneven rows.

Editorial illustration for Split a narration into columns by slash with Text to Columns

To split a narration by slash, select the narration column, open Data, then Text to Columns, choose Delimited, tick Other and type a slash. Pick a destination cell away from your original data so the narration column stays intact. Text to Columns is a one-time action and does not update later, so use it when you want a quick, visible split.

What are the steps?

  1. Copy the narration column to a new sheet, or leave at least four empty columns to its right. Text to Columns overwrites whatever is in its way.
  2. Select the narration cells, not the whole column with the header.
  3. Go to Data, then Text to Columns.
  4. Choose Delimited and click Next.
  5. Tick Other, type / in the box, and untick Tab or any other delimiter that is not in your text. Look at the preview below.
  6. Click Next. Select the column that holds the reference number and set its format to Text, so that digits are kept exactly as written.
  7. In the Destination box, click a cell such as $H$2, away from the original data, then click Finish.

Why set a destination?

If you skip the destination, Excel splits in place and replaces the original narration with its first segment. Undo works straight afterwards, but not after you have saved and closed. Splitting into a separate area leaves the full narration beside the pieces, so you can compare and rebuild.

What happens when rows have different numbers of segments?

Excel makes as many columns as the longest row needs. A row with fewer slashes simply has empty cells on the right, which is harmless. The real problem is a row where one extra slash, for example in a name, pushes everything one column across. Before you split, count the slashes in each row:

  • =LEN(A2)-LEN(SUBSTITUTE(A2,"/","")) gives the number of slashes in A2.
  • Fill it down, then filter or sort by that count.
  • Split the common group first, then deal with the odd group separately.
Invented narrationSlashesAfter splitting
UPI/412345678901/SHARMA TEA STALL/sharmatea@okbank3Four columns, name in the third
UPI/412345678902/RAJ AND SONS/rajsons@okbank/Payment for order4Five columns, a note in the fifth
UPI/412345678903/KUMAR STORES2Three columns, nothing to the right

Should consecutive slashes count as one?

There is a tick box called Treat consecutive delimiters as one. Leave it off for narrations. If a field is blank and you tick it, the segments to its right shift one place left, and your name column will quietly hold a handle for some rows. With it off, a blank field leaves an empty cell and everything else stays in its column.

What if you want it to update?

Text to Columns is a manual, one-off action. If next month's rows need the same treatment, you have to repeat it, or switch to a method that recalculates:

  • In Google Sheets, =SPLIT(A2,"/") spills the segments across the row, and Data, then Split text to columns does the one-time version.
  • In Excel 365, TEXTSPLIT does the same job with a formula, which keeps working as you add rows.
  • Power Query splits on every refresh, if you load the data as a query.

A short routine for each month's statement

  1. Paste the new rows below the previous month, in the same columns.
  2. Run the slash count and look at any row that differs from the usual.
  3. Split only the new rows, to a destination beside them.
  4. Format the reference column as Text.
  5. Add a header to each new column so a filter will work.

When the monthly paste-and-split starts to feel like a chore, SpendixAI can take the statement as PDF, CSV or Excel and show you each row for review before it is saved. For a quick look at one month, though, Text to Columns is perfectly good.

SPENDIXAI JOURNAL

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

Explore your reports