Text
VALUE function: convert numbers stored as text into real numbers
Turn imported numeric text strings like "145.50" or "0042" into calculable numbers with VALUE, and strip unit labels that cause #VALUE!.
Why SUM skips numeric strings—and how VALUE fixes them
When an ERP export quotes numbers as text ("145.50", "0042", "-18"), aggregate functions like SUM and AVERAGE ignore those text cells when referenced in a range; AVERAGE excludes them from its count rather than treating them as zeros. VALUE(text) converts a numeric string into a true number (145.5, 42, -18) and passes already-numeric cells through unchanged.
Extracting digits with LEFT, MID, or RIGHT always produces text; wrapping the extraction in VALUE makes the result ready for math or numeric lookups.
| Cell | Stored Content | Bare VALUE(Cell) | Cleaned Formula |
|---|---|---|---|
| A2 | ="145.50" (text) | 145.5 | 145.5 |
| A3 | ="0042" (text) | 42 | 42 |
| A4 | ="-18" (text) | -18 | -18 |
| A5 | ="85 kg" (unit suffix) | #VALUE! | =VALUE(SUBSTITUTE(A5, " kg", "")) -> 85 |
Convert clean numeric text and summing results
Once converted with VALUE, standard addition and SUM include every item accurately (145.5 + 42 - 18 = 169.5).
=VALUE(A2) + VALUE(A3) + VALUE(A4)Strip unit suffixes before calling VALUE
Trailing letters like "85 kg" or "12 pcs" cannot be parsed as a number and raise #VALUE!. Removing the unit text with SUBSTITUTE or TEXTBEFORE first lets VALUE succeed.
=VALUE(SUBSTITUTE(A5, " kg", ""))Try the example
Use the original values or change them to test your understanding.