Clean Bank Statement Descriptions in Excel with TRIM and CLEAN

Transaction descriptions copied from a statement or imported from another application can carry irregular spacing or hidden characters. Two cells can look the same on screen yet fail a lookup or a duplicate check, because one has a stray space or a control character.

Microsoft describes both TRIM and CLEAN as intended for text that comes from other applications.

TRIM(text) "removes all spaces from text except for single spaces between words," so leading and trailing spaces go, and repeated spaces between words collapse to one. The argument can be a quoted string or a cell reference.

With a synthetic description in A2 such as " SAMPLE MARKET " (two spaces in front, three between the words, two at the end):

CLEAN(text) removes nonprintable characters. Microsoft says it was designed to remove the first 32 nonprinting characters in 7-bit ASCII (values 0 through 31). Its example: if A2 contains =CHAR(9)&"Monthly report"&CHAR(10), then =CLEAN(A2) returns "Monthly report", stripping the tab and the line feed.

Apply it the same way:

TRIM was designed to remove the 7-bit ASCII space character, value 32. Microsoft states that the Unicode nonbreaking space, decimal value 160 and common on web pages, is not removed by TRIM by itself.

Likewise, CLEAN does not remove some Unicode nonprinting characters (values 127, 129, 141, 143, 144 and 157). Microsoft's data-cleaning guidance says to use SUBSTITUTE to replace the higher-value Unicode characters (127, 129, 141, 143, 144, 157 and 160) with ASCII characters that TRIM and CLEAN can handle.

SUBSTITUTE(text, old_text, new_text, [instance_num]) replaces every occurrence of old_text when instance_num is omitted. CHAR(number) returns the character for a code from 1 to 255 in the character set your computer uses. Combining them for value 160:

Formulas leave the original text in place. Put the formula in a helper column, then use the cleaned column for lookups. If you need values rather than formulas, Microsoft documents copying formula results and choosing Paste Special > Values in a related article on converting text numbers.

If the transactions are still in a PDF, PDFtoRow converts bank statement PDFs to Excel or CSV and shows a free preview of the first 10 rows.