Xero™ export numbers that Excel™ will not add up: brackets, commas and hidden spaces
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
- Parenthesised negatives, such as (45.00), which Excel™ may read as text depending on how the cell is formatted.
- Comma decimals, as in 45,00, in a file that uses a comma as the decimal separator.
- Spaces at the start or end of a value, including non-breaking spaces.
- Total rows or blank rows sitting inside the data, which stop a clean sum.
Check by hand
These steps apply to Excel™ in Microsoft 365. Work on a copy.
- Select the column and check the status bar. A count with no sum means the cells are text.
- 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. - 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.
- For spaces,
TRIMremoves ordinary ones. It does not remove the non-breaking space, so useSUBSTITUTE(A2,CHAR(160),"")as well. - Once the column converts, set the cell format to Number.
- 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.