Convert Text to Number in Excel and Google Sheets

A bank export lands in column B and it looks correct: $1,234.50 in B2, $980.00 in B3, $2,150.75 in B4. Then =SUM(B2:B6) returns 0. Nothing is corrupted. The cells contain text that happens to read like currency, and SUM ignores text inside a range rather than guessing what it meant. COUNT reports 0 numbers in the same range, while COUNTA reports 5 because it only checks whether a cell is empty. AVERAGE throws #DIV/0! for the matching reason: no numeric inputs at all. The fastest visual check is alignment. Real numbers sit against the right edge of a cell by default, text sits against the left.

VALUE(text) parses a single string using the number, date and time formats Excel recognizes and returns a real number. Microsoft's own example is =VALUE("$1,000") with a result of 1000, so a currency symbol and a thousands separator need no cleanup first. Percent signs become decimals, and date strings become date serials. The argument can be a quoted literal or a reference to a cell holding the text.

Arithmetic pushes text into a number. =B2*1, =B2+0 and the double unary =--B2 apply the same parsing rules VALUE uses, with two differences: they are quicker to fill down a thousand rows, and they give you no separate error message to read. A string that fails comes back as #VALUE! either way. A blank cell comes back as 0, which is where the shortcut earns its reputation for causing trouble.

Some strings look perfect and still fail. Web exports and CSV files often carry a non-breaking space, character 160, which renders exactly like an ordinary space. TRIM removes ordinary padding and collapses runs of spaces, yet it leaves 160 untouched, in Excel and in Google Sheets. VALUE then meets a character no number format allows and returns #VALUE!, which sends people hunting for a formatting problem that does not exist.

VALUE fixes values, not meaning. It strips a currency symbol without recording which currency it was, so $1,234.50 and €1,234.50 both arrive as 1234.5 and the report loses the distinction. It also cannot split a cell that holds a number plus a unit label, such as "1200 USD". Google Sheets ships VALUE with the same name, syntax and behavior, and its own documentation uses =VALUE("01/01/2012") returning 40909 to make the same point about dates. Sheets adds TO_PURE_NUMBER, which sounds like the answer and is not: it removes formatting from a value that is already numeric, and hands back text unchanged. Sheets also has no green error triangle, so ISNUMBER is the reliable test in both apps.