Kayabyte

Numbers stored as text in many Excel files: why SUM shows 0, and how to fix every file on a Mac

By Kayabyte · Last updated 11 November 2026

A column of numbers that Excel treats as text will not add up: SUM ignores those cells, sorting puts “100” before “20”, and lookups fail. In Excel for Mac you can fix one column at a time with the error alert, Text to Columns or Paste Special, Multiply (checked 11 November 2026 against Microsoft’s page on converting numbers stored as text). For a folder of workbooks, Gridhand’s Clean tool has a Numbers step that turns text that looks like a number into a number in every file, with a preview of examples and counts first. It takes .xlsx and .xlsm files only; a CSV goes through Convert first.

How to tell a number is really text

The manual fixes in Excel for Mac

Microsoft’s page lists these, and they work one range at a time:

  1. The error alert. Select the flagged cells, open the warning menu next to them and choose the option to convert to a number.
  2. Text to Columns. Select the column, choose Data, Text to Columns and click Finish. Then press Command-1 to pick the number format you want.
  3. Paste Special, Multiply. Type 1 in a blank cell and copy it. Select the text numbers, choose Home, Paste, Paste Special, Multiply and click OK.
  4. The VALUE function. In a new column, point VALUE at the text cell. This keeps the original column.

One caution that applies to all of them: a code such as 00123 (a ZIP code, a part number) is a text on purpose. Converting it removes the leading zeros.

Why this does not scale

With 30 monthly exports, each with several sheets, you repeat the steps on every sheet of every file, and nothing tells you which cells you skipped. Different files often have the problem in different columns.

With Gridhand

Gridhand Clean has a Numbers step: text that looks like a number becomes a number. Codes with leading zeros and codes of 12 or more digits stay text, so IDs and long account numbers are not damaged. The preview shows examples and counts before anything is written. You can tick the Spaces step in the same run, which trims the ends of text and collapses repeated and non-breaking spaces, a common reason a number is not recognised as one.

Results are new copies in the output folder you choose, with the file type kept (macros included). Your originals are never modified. The result lists notes under Check these, Left out, Changed on the way and Good to know; Copy Report copies them as text.

Gridhand Clean on a Mac: a preview of cleaned cells and the cleaned copy written next to the original

Steps with Gridhand

  1. Drop the workbooks, or a folder, into Gridhand and choose Clean.
  2. Tick Numbers, and Spaces if the text has stray spaces. Leave the other steps off.
  3. Read the preview: examples and counts per step.
  4. Choose an output folder and press Run, then open the report and read the notes marked Check these.

Free for your first 2 runs (up to 2 files each), then Gridhand Pro is USD 49 once. Previews are always free. Download Gridhand

Questions

Will it strip the leading zeros from my ZIP codes? No. Codes with leading zeros stay text, and so do codes of 12 or more digits.

What about a CSV? Gridhand’s other tools take .xlsx and .xlsm only. Run the CSV through Convert, which makes real numbers only when the whole column agrees, then Clean the result if needed.

Does it change the originals? No.

Microsoft and Excel are trademarks of the Microsoft group of companies. Gridhand is made by Kayabyte and is not affiliated with or endorsed by Microsoft.