Google Sheets numbers stored as text look like amounts but are strings, so SUM skips them and totals come out short. Fix them with a helper column: =VALUE(A2) for clean values, or SUBSTITUTE first to strip currency signs and spaces. If decimal commas are involved, check the spreadsheet locale before writing any formula.
How can you tell Google Sheets numbers are stored as text?
Google Sheets numbers stored as text give themselves away in three places: they sit against the left edge of the cell, a SUM over them returns less than you expect, and =ISNUMBER(A2) returns FALSE. Real numbers align right by default. One left-aligned value in a column of right-aligned ones is the usual sign.
Run the test on a whole column at once. In an empty cell, =COUNT(B2:B200) counts only real numbers, while =COUNTA(B2:B200) counts every filled cell. If the two differ, the gap is the number of text values hiding in the column.
Imports cause most of these. A CSV pasted as plain text, a column formatted as Plain text before the paste, or a value with a leading apostrophe all keep the digits as a string. Statement data from a PDF adds its own extras, such as a dollar sign or a trailing space copied along with the amount.
Why does the Format menu leave Google Sheets text numbers unchanged?
The Format menu changes how Google Sheets displays a value, not what the value is. When a cell already holds the string "52.18", choosing Format, then Number, then Number gives it a number format, but the stored content stays text. The cell has to be re-entered or recalculated before Sheets reads it as a number.
You can see this by setting the format and then checking with ISNUMBER again. It still says FALSE. Double-clicking a cell and pressing Enter often fixes that one cell, because Sheets re-reads the typed content under the new format. That works for five cells and is tedious for five hundred.
The one case where formatting first does help is before the data arrives. If a column is set to Plain text and you paste into it, everything stays text. Set the column to Automatic, then paste, and Sheets parses the digits as it goes.
Converting text amounts with VALUE and SUBSTITUTE in Google Sheets
VALUE converts a text string that looks like a number into a real number, so =VALUE(A2) in a helper column fixes clean values. When the text carries extra characters, wrap it in SUBSTITUTE to remove them first. Once the helper column is right, copy it and paste values only over the original.
The table shows example raw values and a formula that converts each. The values are illustrative.
| Raw cell (text) | Formula | Result |
|---|---|---|
| 52.18 | =VALUE(A2) |
52.18 |
| $1,250.00 | =VALUE(SUBSTITUTE(A3,"$","")) |
1250 |
| 61.40 followed by a space | =VALUE(TRIM(A4)) |
61.4 |
| 61.40 followed by a non-breaking space | =VALUE(SUBSTITUTE(A5,CHAR(160),"")) |
61.4 |
| 18.00- | =-VALUE(SUBSTITUTE(A6,"-","")) |
-18 |
To apply a formula down the column, write it in the first row of the helper column and fill it down. Then select the helper column, copy, and use Edit, Paste special, Values only onto the original column. Delete the helper column afterwards.
Multiplying by one works too: =A2*1 forces Sheets to read the text as a number. It fails on the same characters VALUE fails on, so it is a shortcut, not a different fix.
Currency symbols, stray spaces and trailing minus signs in text amounts
Currency symbols, stray spaces and trailing minus signs are the characters that most often keep a statement amount as text. VALUE accepts some of them and rejects others. If VALUE returns an error, look at the cell's length with LEN: a result longer than the visible digits means a hidden character is still there.
Things to strip, roughly in order of how often they appear in converted statements:
- Ordinary spaces at either end. TRIM removes them.
- Non-breaking spaces, which look the same but are character 160. TRIM leaves them, so SUBSTITUTE them away. TRIM and CLEAN for imported text in Google Sheets covers this and other hidden characters.
- Currency codes such as "USD" printed next to the amount.
- Trailing minus signs or "DR" and "CR" markers, which carry the sign and need turning into a leading minus rather than deleting.
- Parentheses around negative amounts.
Dates have the same problem and a different fix. A date stored as text needs DATEVALUE or a rebuild from its parts, which fixing dates in Google Sheets after an import explains step by step.
Decimal commas and the spreadsheet locale behind stuck text numbers
A spreadsheet's locale decides which character Google Sheets treats as the decimal separator. Google's help page on changing a spreadsheet's locale says a locale change alters the default currency, date and number formatting. A sheet set to United States reads "1.234,56" as text, because a comma cannot be the decimal mark there.
This is the edge case that no formula tidy-up fixes cleanly. You have two options:
- Match the locale to the data. Open File, then Settings, and pick a locale that uses decimal commas, such as Germany. The change applies to the whole spreadsheet for everyone who opens it, and it does not change the language of the menus. Re-import the data after the change.
- Swap the separators. Keep a US locale and convert with
=VALUE(SUBSTITUTE(SUBSTITUTE(A2,".",""),",",".")), which removes the thousands dots and turns the decimal comma into a point.
Pick one approach per sheet and write it down. Mixing them produces values that are off by a factor of a thousand. Turning a PDF into a Google Sheets spreadsheet covers setting the locale before the import, which avoids the conversion step entirely. If you work on a Mac, the same separator issue shows up when you move a PDF to Apple Numbers.
A converter that writes amounts as real numbers removes this step at the source. FastBankConvert, which is getting ready to open, is built to put each statement amount in its own column of an Excel or CSV file; you can join at Get started.