AppGarden Import Cleaner for Xero logo AppGarden

Import Cleaner for Xero

Xero™ export numbers that Excel™ will not add up: brackets, commas and hidden spaces

By Paul Kemp · Published

You open a Xero™ export in Excel™ and SUM comes back short. The figures look right. Some of them are text.

What turns a number into text

Check by hand

These steps apply to Excel™ in Microsoft 365. Work on a copy.

  1. Select the column and check the status bar. A count with no sum means the cells are text.
  2. For brackets, a helper column such as =IF(LEFT(A2,1)="(", -VALUE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","")), VALUE(A2)) converts them. This assumes a full stop as the decimal separator.
  3. For comma decimals, check which separator your data uses before you replace anything. Replacing commas in a column that also holds thousands separators changes the amounts.
  4. For spaces, TRIM removes ordinary ones. It does not remove the non-breaking space, so use SUBSTITUTE(A2,CHAR(160),"") as well.
  5. Once the column converts, set the cell format to Number.
  6. Spot-check a few rows against the original file.

Let a check find them

Import Cleaner for Xero™ checks the active sheet in the "Out of Xero" direction for numbers stored as text, bracketed negatives, comma decimals, stray spaces and totals inside the data, and explains each finding by cell. The scan, every explanation, the stray-space fix and Undo are free on every tier; applying the other fixes is the paid part. The check runs inside Excel™ and the 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.