Data Cleaningbeginner
Remove extra spaces from text with TRIM
=TRIM(A2)
Excel how-to guide
How to value convert text number in Excel
Fix imported spreadsheet data where amounts, quantities, or IDs are left-aligned and treated as text, silently excluded from calculations.
Step-by-step instructions
How to value convert text number — 3 steps
1
Wrap the text cell in TRIM first to remove any leading or trailing spaces that would cause VALUE to fail.
2
Pass the trimmed value to VALUE — it converts numeric text strings to real numbers.
3
Wrap in IFERROR if some cells contain truly non-numeric text that should default to zero or blank.
Example data
Worked example
| Text Amount | Numeric Amount |
|---|---|
| '1500 | 1500 |
| 250 | 250 |
| 3000 | 3000 |
Common mistakes
Errors to watch out for
VALUE returns
The cell contains characters that cannot be parsed as a number — commas, currency symbols, or letters. Remove them with SUBSTITUTE before applying VALUE.
Result is still treated as text
After using VALUE, ensure the result cell is formatted as Number, not Text. A cell format of Text overrides formula results.
FAQ
Frequently asked questions
Is there a faster way to convert a whole column?
Select the column, go to Data > Text to Columns > Finish — this converts the selection in place without a helper column. VALUE is better for formula-driven pipelines.
Describe your exact spreadsheet problem — get the formula in seconds.
Open AI Formula Generator — FreeRelated how-to guides
Continue learning.
Data Cleaningbeginner
Validate numeric entries with ISNUMBER
=ISNUMBER(A2)