Spreadsheets

Remove extra spaces and line breaks from narrations: TRIM and CLEAN

Remove extra spaces and line breaks from narrations in Excel or Sheets with TRIM, CLEAN and SUBSTITUTE(CHAR(10)), and fix lookups that fail on spaces.

Editorial illustration for Remove extra spaces and line breaks from narrations: TRIM and CLEAN

To clean a narration in Excel or Google Sheets, use =TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A2,CHAR(10)," "),CHAR(160)," "))). SUBSTITUTE swaps line breaks and non-breaking spaces for ordinary spaces, CLEAN strips other invisible characters, and TRIM removes leading and trailing spaces and shrinks double spaces to one. The order matters, and the reasons are below.

What does each function do?

FunctionRemovesLeaves behind
TRIMSpaces at the start and end; repeated spaces inside become oneLine breaks, non-breaking spaces (CHAR 160)
CLEANNon-printing characters such as CHAR(10) line feeds and CHAR(13) returnsSpaces; and it joins words that were split by a line break
SUBSTITUTE(A2,CHAR(10)," ")Line feed, replaced by a space of your choiceEverything else
SUBSTITUTE(A2,CHAR(160)," ")Non-breaking space, a lookalike of a normal spaceEverything else

Why does order matter? CLEAN deletes a line break without adding a space, so "UPI-ZOMATO" followed by a break and "ORDER" would join into "UPI-ZOMATOORDER". Replace the break with a space first, and CLEAN is left with the rarer invisible characters.

Why does wrapped PDF text leave line breaks?

A statement PDF is laid out in columns, and a long narration wraps to fit the column. When you copy it, each wrapped line often arrives as a break inside the cell, so one transaction looks like two or three lines. The text also tends to bring double spaces where the PDF aligned things visually. The cell looks fine on screen and misbehaves in formulas.

A worked example you can test

Say A2 holds " Rent to landlord " with two spaces at the start, two between each pair of words and one at the end. Count it: 2 leading, 4 for Rent, 2, 2 for to, 2, 8 for landlord, 1 trailing, which is 21 characters. After =TRIM(A2) it reads "Rent to landlord", which is 4 plus 1 plus 2 plus 1 plus 8, or 16 characters. A formula like =LEN(A2)-LEN(TRIM(A2)) returns 5, the number of spaces removed. Use it as a detector: any row where it is above 0 has extra spaces.

Why does my lookup fail when the text looks identical?

Lookups compare every character, including invisible ones. "Swiggy" and "Swiggy " are different to VLOOKUP, XLOOKUP, MATCH and COUNTIF, so a category rule or a merchant list silently finds nothing. To see it, put =A2=B2 in a spare cell, or =LEN(A2) next to =LEN(B2); a mismatch of one or two means a hidden space or break. Clean the column first and run the lookup on the cleaned values.

How do I clean a whole column and keep the result?

  1. Insert a helper column next to the narrations and fill the formula down, for example in B2: =TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A2,CHAR(10)," "),CHAR(160)," "))).
  2. Copy the helper column and use Paste special, then Values only, over the original (or into a new column).
  3. Delete the helper column once you have checked a handful of rows against the original.
  4. In Google Sheets, one formula can clean the entire column: =ARRAYFORMULA(TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A2:A500,CHAR(10)," "),CHAR(160)," ")))). Excel 365 spills the same formula without ARRAYFORMULA.
  5. Run =SUMPRODUCT(--(LEN(B2:B500)<>LEN(TRIM(B2:B500)))) and expect 0; any other number counts the rows with spaces left.

Should I clean the data before or after categorising?

Before. Spaces and breaks are the usual reason a rule meant to catch every Zomato row catches half of them. Tidy the text, then build rules on the cleaned column and keep the original untouched in case you need to check the source line.

SpendixAI works on a normalised merchant name when it categorises an imported statement, and you can add merchant aliases when the same shop appears under different narrations, which saves some of this cleaning when your source is a bank statement.

SPENDIXAI JOURNAL

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

Explore your reports