Spreadsheets

SPLIT function in Google Sheets: break narrations into columns

Learn the SPLIT function in Google Sheets to break a slash-separated bank narration into columns, pick one part with INDEX, and know when the menu command is better.

Editorial illustration for SPLIT function in Google Sheets: break narrations into columns

To break a narration into columns in Google Sheets, use =SPLIT(A2, "/"). It cuts the text in A2 at every slash and spreads the pieces across the cells to the right, one piece per cell. To keep just one piece, wrap it as =INDEX(SPLIT(A2, "/"), 1, 3), which returns the third piece of the first (and only) row.

What does SPLIT do to a bank narration?

Bank and UPI narrations often pack several facts into one cell, with slashes between them. Say cell A2 holds this made-up narration: UPI/431256789012/Sharma General Store/Groceries. The formula below goes in B2 and fills four cells, B2 to E2.

  • =SPLIT(A2, "/") gives UPI in B2, 431256789012 in C2, Sharma General Store in D2 and Groceries in E2.
  • The first argument is the text (a cell or a quoted string). The second is the separator, which can be any character or short piece of text in quotes.
  • The pieces keep their order, so the merchant sits in the same position on every row as long as the bank uses one layout.

SPLIT turns anything that looks like a number into a number. A reference that starts with zeros will lose them, and a very long one may show in scientific notation. If the reference matters, compare it with the original column before you rely on it.

How do I pick just one part with INDEX?

Often you only want the merchant. INDEX can reach into the array that SPLIT returns. The arguments are the array, the row number and the column number. Because SPLIT gives a single row, the row is always 1.

GoalFormulaResult for the sample narration
Payment mode=INDEX(SPLIT(A2, "/"), 1, 1)UPI
Merchant=INDEX(SPLIT(A2, "/"), 1, 3)Sharma General Store
Note at the end=INDEX(SPLIT(A2, "/"), 1, 4)Groceries

Fill the formula down the column and every row gives its own merchant. If a row has fewer pieces than you asked for, INDEX returns an error such as #REF!. Wrap it as =IFERROR(INDEX(SPLIT(A2, "/"), 1, 3), "") to leave the cell blank instead.

Can I split a whole column in one formula?

Yes. Put =ARRAYFORMULA(SPLIT(A2:A500, "/")) in B2 and leave the cells below and to the right empty. One formula then fills the whole block, and new narrations pasted into column A further down are picked up as long as they sit inside the range. Use a fixed range such as A2:A500 rather than the whole column so the sheet stays quick.

SPLIT or the Split text to columns menu: which should I use?

Both cut text at a separator, but they behave very differently.

PointSPLIT formulaData > Split text to columns
Type of resultLive formula that updates when A2 changesOne-time command that writes plain values
Original textStays untouched in column AIs replaced by the first piece
Overwrites other cellsShows an error insteadWrites over the cells to its right
Best forA column you keep pasting new rows intoA one-off clean-up of an old export

After using the menu command, a small box offers a separator choice such as comma, semicolon, period, space or a custom character. Type the slash as the custom separator. Check that the columns to the right are empty before you run it, because nothing asks you to confirm.

What if the narration uses more than one kind of separator?

Banks do not use one style everywhere. Some narrations mix slashes, dashes and spaces. A separate article in this series covers splitting on several characters at once. For now, a quick trick is to make the separators uniform first with =SPLIT(SUBSTITUTE(A2, "-", "/"), "/"), which turns every dash into a slash and then splits on slashes.

A short routine for a monthly statement

  1. Paste the narration column into a tab named Raw, starting at A2.
  2. In the next tab, use =ARRAYFORMULA(SPLIT(Raw!A2:A500, "/")) so Raw stays exactly as the bank gave it.
  3. Look at five or six rows and note which position holds the merchant. Layouts differ between banks and even between payment types at one bank, so check your own statement.
  4. Pull the merchant with INDEX only for the rows that follow the pattern, and review the rest by eye.

If you find yourself repeating this clean-up every month, an importer may save the effort. SpendixAI accepts a bank statement in PDF, CSV or Excel and lets you review the rows before saving, so the narration clean-up becomes part of that review instead of a formula chore.

SPENDIXAI JOURNAL

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

Explore your reports