Formula guide · Excel and Google Sheets · Updated May 13, 2026
Numbers Stored as Text
Fix numbers stored as text so SUM, lookup, pivot tables, and comparisons work correctly.
Quick Answer
If numbers are stored as text, convert them with VALUE, multiply by 1, or use a cleanup step before formulas and pivots.
Copyable Formula
=VALUE(SUBSTITUTE(A2,",",""))Syntax
=VALUE(text_number)Excel and Google Sheets
Worked Example
| A | B |
|---|---|
| Raw value | Number |
| 1,200 | 1200 |
| 85 | 85 |
Result: Returns real numbers that can be summed, averaged, compared, and used in pivots.
Steps
- Check suspicious values with ISNUMBER.
- Remove commas, currency symbols, or spaces if needed.
- Use VALUE to convert cleaned text into a number.
- Format the result as a number, not text.
Common Mistakes
- Assuming a value is numeric because it is right-aligned or looks numeric.
- Converting only one column while lookup keys remain mixed text and numbers.
- Ignoring imported non-breaking spaces.
Excel vs Google Sheets Notes
The core idea works in both Excel and Google Sheets, but separators, function availability, and array behavior can vary by account, locale, and version.
Editorial check: This guide was last updated May 13, 2026. Formula behavior can vary by Excel version, Google Sheets rollout, and spreadsheet locale.