Fix guide

Numbers stored as text in Google Sheets™? Find and fix them

Numbers stored as text are one of the quietest spreadsheet bugs. The cells look numeric, but formulas like SUMIFS, COUNTIFS, averages, pivots, and charts can skip or misread them. Here is how to find the problem, clean it safely, and keep it from coming back.

The 10-second test

If column D should contain only numbers, paste this into any empty cell:

=COUNTA(D2:D1000)-COUNT(D2:D1000)

If the result is 0, every populated value in that range is numeric. If the result is greater than 0, some populated cells are not real numbers.

Important: This test counts every non-numeric populated cell in the range. If your range includes header text, labels, notes, or blanks that should not be part of the numeric column, exclude them before interpreting the result.

Try the sample data

Want a quick hands-on version? Download the sample TSV, paste it into a blank Google Sheet, and compare the broken SUMIFS against the cleaned helper column.

Sample file: Download the SUMIFS text-number demo data. It includes paid invoice rows where some amounts look numeric but are stored as text, plus diagnostic and fixed formulas.

Why this breaks SUMIFS and COUNTIFS

Google Sheets can display 15000 and "15000" almost the same way. Humans see both as numbers. Formulas do not. A text-stored amount can be ignored by arithmetic, mis-grouped in pivots, or fail criteria comparisons.

A | B | C Customer | Status | Amount Acme | Paid | 15000 Beta | Paid | "12000" Core | Paid | "$8,500" =SUMIFS(C:C, B:B, "Paid") may return a total that is too low.

The dangerous part is that the answer often looks plausible. There is no #VALUE! warning. The dashboard is just wrong.

Common causes

  • CSV imports where numeric columns arrived as text.
  • Currency symbols like $, Rs., or EUR inside the amount cell.
  • Thousands separators such as 15,000 imported as text.
  • Leading apostrophes, for example '15000.
  • Spaces before or after the value, for example " 15000 ".
  • Shorthand values such as 15K, 1.2M, or 8 lacs.
  • Dates stored as text, which break date-based SUMIFS criteria.

Step 1 - Locate the exact bad rows

The count tells you there is a problem. To find the rows, add a helper column beside the imported numbers:

=AND(D2<>"", NOT(ISNUMBER(D2)))

Fill it down. Any row returning TRUE contains a populated value that is not a real number.

If you want to flag the issue visually, use conditional formatting with a custom formula:

=AND($D2<>"", NOT(ISNUMBER($D2)))

Step 2 - Clean simple currency and comma cases

For values like $15,000, use a helper column and strip the symbols before converting:

=VALUE(SUBSTITUTE(SUBSTITUTE(TRIM(D2),"$",""),",",""))

For Indian rupee text such as Rs. 15,000:

=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(D2),"Rs.",""),"Rs",""),",",""))

After converting, keep the original column until you verify totals. Then copy the cleaned values and paste special as values if you want to replace the source column.

Step 3 - Convert percentages carefully

A value like 15% may be text. If so, convert it to a decimal number:

=VALUE(SUBSTITUTE(TRIM(D2),"%",""))/100

Format the result column as Percent. Do not skip the /100, or 15% becomes 15 instead of 0.15.

Step 4 - Handle shorthand values manually

Values like 15K, 1.2M, or 8 lacs are not safe to bulk-convert unless your dataset uses one consistent convention. A simple formula can parse some of them, but review the column first.

=IF(RIGHT(UPPER(TRIM(D2)),1)="K", VALUE(LEFT(TRIM(D2),LEN(TRIM(D2))-1))*1000, VALUE(D2) )

For messy finance or sales data, it is safer to create a cleaned helper column and spot-check 10-20 rows before replacing anything.

Step 5 - Check date columns too

Dates are numbers internally in Google Sheets. If your date column is text, date criteria in SUMIFS can skip rows.

For a date in A2, test:

=ISNUMBER(A2)

For the whole date column:

=COUNTA(A2:A1000)-COUNT(A2:A1000)

If the column should be all dates, a non-zero result means some dates are stored as text.

Step 6 - Point formulas at the cleaned column

Once you create a numeric helper column, update formulas and pivots to use it. Do not keep summing the original imported column.

Risky: =SUMIFS(D:D, B:B, "Paid") Safer: =SUMIFS(E:E, B:B, "Paid") Where E:E is the cleaned numeric version of D:D.

Quick checklist

Count non-numeric values: =COUNTA(D2:D1000)-COUNT(D2:D1000) Find bad rows: =AND(D2<>"", NOT(ISNUMBER(D2))) Clean $ and commas: =VALUE(SUBSTITUTE(SUBSTITUTE(TRIM(D2),"$",""),",","")) Clean percentages: =VALUE(SUBSTITUTE(TRIM(D2),"%",""))/100 Check date cells: =ISNUMBER(A2)
Where SheetBrain helps: SheetBrain's free Check tab flags mixed text/number columns with the mixed_text_number detector. It is designed for exactly this kind of silent spreadsheet bug: a column looks numeric, but a formula may not treat every row as numeric.

Find text-stored numbers before they break totals

Install SheetBrain and run the free Check tab. It scans the active sheet for mixed text/number columns, visible formula errors, broken references, duplicate lookup keys, formula pattern outliers, and missing copied formulas. No AI quota, no API key, no credit card.

Install free on Google Workspace

TL;DR

  • Use =COUNTA(range)-COUNT(range) to count populated cells that are not real numbers.
  • Use =AND(cell<>"", NOT(ISNUMBER(cell))) to locate exact rows.
  • Clean in a helper column first. Verify totals before replacing the source data.
  • Watch dates too. Text-stored dates break date criteria in SUMIFS.
  • Use SheetBrain Check when you want a quick scan for mixed text/number columns across the active sheet.

Related