Numbers from Xero™ won't sum in Google Sheets™
The classic symptom: a column of amounts where =SUM() returns far less than it should, or zero. The numbers are not numbers. They are text wearing a number costume: currency symbols, thousands separators in odd places, trailing spaces, or a leading apostrophe from the export.
Why exports arrive as text
A CSV carries no type information, so Google Sheets™ guesses each cell from its content. Anything it cannot parse as a number in the spreadsheet's locale stays as text. Watch for:
- Currency symbols typed into the value (
£1,234.50instead of1234.50with a £ format) - Spaces before or after the digits
- Parentheses or minus signs in unfamiliar positions
- Decimal commas in a decimal-point locale, or the reverse
Check by hand
- Test the column with
=ISNUMBER()copied down. FALSE means text. - For currency symbols and spaces, select the column and use Edit → Find and replace to strip them, then check again.
- For locale mismatches (commas vs points), fix the spreadsheet locale or re-import rather than editing thousands of cells.
- Format the cleaned column as a number or currency with Format → Number, so the next reader sees what the cells hold.
Find every text-number at once
Import Cleaner for Xero scans a Xero™ export for amounts stored as text, broken dates and contact names that would create duplicates. The full scan, every explanation and Undo are free, and your data stays in your Google™ account.
Install from the Google Workspace Marketplace™
Google, Google Sheets™ and Google Workspace™ are trademarks of Google LLC. Not affiliated with, endorsed by or sponsored by Xero Limited. Xero is a trademark of Xero Limited. Import Cleaner for Xero is not affiliated with or endorsed by Google.
Google, Google Sheets™ and Google Workspace™ are trademarks of Google LLC. Not affiliated with, endorsed by or sponsored by Xero Limited. Xero is a trademark of Xero Limited. Import Cleaner for Xero is not affiliated with or endorsed by Google.