TRIM
Removes extra spaces from text, leaving one space between words and none at the ends.
TRIM(<text>)Returns: Text (a scalar).
What it does
TRIM removes spaces from the start and end of a text value and shortens any run of spaces between words to a single space. Exports from accounting systems and spreadsheets often pad names with spaces. Those spaces make two identical names look different to a match. TRIM is the first step of nearly every cleaning formula.
Syntax and arguments
- text: the value to clean, a column or a text value.
Example
Bank descriptions come from a feed that pads the payee name with spaces. The payment matching needs the clean name.
Payee Clean = TRIM(BankLines[PayeeName])
A payee shown as " ACME SUPPLY " becomes "ACME SUPPLY". The clean value is used as the key for the match against the vendor list.
Example: a second, different use
Count how many vendor names contain a double space, which signals a manual entry problem:
Names Needing Clean = IF(LEN(Vendors[VendorName]) <> LEN(TRIM(Vendors[VendorName])), 1, 0)
If trimming shortens the name, the original had extra spaces. The sum of the flags is the size of the clean-up job.
Things to watch
- TRIM handles ordinary spaces. Non-breaking spaces and some other characters from web or PDF exports can survive. Check the sample data and use SUBSTITUTE for those.
- TRIM does not change the case. Use UPPER or LOWER for that.
- TRIM does not remove spaces inside a value that you might want removed, such as the space in a phone number. Use SUBSTITUTE for those.
- A blank input gives an empty result.
- Clean the data at the source when possible. A calculated column with TRIM costs memory on large tables.
In business terms
TRIM is the simplest and most effective cleaning step in finance data work. It makes bank descriptions match vendor names, makes account names match the chart of accounts and prevents a payee from appearing twice in a report.