How to Convert Text to Numbers in Google Sheets
Type =VALUE(A2) to convert a number that is stuck as text (left-aligned) in A2 into a real number you can sum and sort.
=VALUE(A2)
VALUE parses a text string that looks like a number into an actual number. Left-aligned entries are the tell-tale sign of text.
How it works
Numbers imported from other systems often arrive as text — you can spot them because they align to the left and refuse to add up. VALUE converts a single cell; multiplying by 1 or adding 0 does the same thing and handles stray characters slightly differently. For a whole column, wrap VALUE in ARRAYFORMULA. If VALUE returns #VALUE!, the string contains characters it cannot parse, such as currency symbols or thousands separators — strip them with SUBSTITUTE first.
Variations
Multiply-by-1 shortcut
=A2*1
Forces text into a number without a function.
Convert a whole column
=ARRAYFORMULA(VALUE(A2:A))
Fills the column from one cell.
Strip a currency symbol first
=VALUE(SUBSTITUTE(A2,"$",""))
Remove characters VALUE cannot parse before converting.
Examples
| Scenario | Formula |
|---|---|
| Sum a column stored as text | =SUM(ARRAYFORMULA(VALUE(A2:A))) |
| Convert a single imported cell | =VALUE(A2) |
FAQ
How do I know a number is stored as text?
It aligns to the left of the cell and is ignored by SUM. Real numbers align right by default.
Why does VALUE give #VALUE!?
The text has characters VALUE cannot read — a currency sign, a % or a comma separator. Remove them with SUBSTITUTE before converting.
Can I convert without a formula?
Yes — select the range, then Format > Number > Number, or use Data > Split text to columns, which re-parses the values.