AppGarden Import Cleaner for Xero logo AppGarden

Import Cleaner for Xero

The space that TRIM will not remove: Excel™ contact names before a Xero™ upload

By Paul Kemp · Published

A contact imported as "Acme Ltd" and one imported as "Acme Ltd." can end up as two contacts in the live ledger. Merging them afterwards is real work, so it is worth checking the names first.

Why TRIM does not fix it

Excel™'s TRIM removes ordinary spaces. It does not remove a non-breaking space (character 160), which often arrives with text copied from a web page or a PDF. The name looks the same, but it is a different string.

Check by hand

These steps apply to Excel™ in Microsoft 365. Work on a copy of the contacts column.

  1. In a helper column, enter =SUBSTITUTE(A2,CHAR(160)," "), then wrap it in TRIM: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
  2. Compare lengths with =LEN(A2)-LEN(B2). Anything above zero had hidden spaces.
  3. Sort the cleaned column A to Z. Near-duplicates sit next to each other: "Acme Ltd", "Acme Ltd.", "ACME LTD".
  4. Choose one spelling per contact and apply it down the column. Use Data → Remove Duplicates only on a copy, and only after the spelling matches.
  5. If your organisation already holds the contact in Xero™, match the existing spelling exactly.

Let a check group them

Import Cleaner for Xero™ groups contact near-misses before upload, including ones that differ only by a non-breaking space. Each finding names the cells and says why. The scan, explanations, the stray-space fix and Undo are free on every tier. The check runs inside Excel™ and your workbook is not uploaded.

See Import Cleaner for Xero™ for Excel™

Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. Xero is a trademark of Xero Limited. Import Cleaner for Xero is an independent product, not affiliated with or endorsed by Microsoft or Xero Limited.