Quick answer
The Convert Numbers Stored as Text formula turns imported text numbers into real numeric values.
=VALUE(SUBSTITUTE(A2,",",""))Syntax
Use this syntax as the pattern, then replace the sample arguments with your own cells, ranges, and criteria.
=VALUE(text_number)When to use it
Data cleaning formulas save time before analysis. They help repair exported data, standardize columns, remove invisible characters, and prepare spreadsheets for pivot tables, lookups, and dashboards.
- Find duplicate records
- Convert text numbers into real numbers
- Clean phone numbers for consistent formatting
- Create dropdown validation lists
Example worksheet layout
Test the formula in a small worksheet first. This makes it easier to confirm the result before copying the formula down hundreds of rows.
| Column or range | What it represents |
|---|---|
| A | Original imported value |
| B | Cleaned value or flag |
| C | Check/result column |
Copy-paste examples
These examples move from simple to more practical versions. Start with the beginner examples, confirm the result, then adapt the intermediate and advanced versions.
Basic Convert Numbers Stored as Text example
=VALUE(SUBSTITUTE(A2,",",""))Use the simple version first and compare the result to a manual check.
Copy down a table
=VALUE(SUBSTITUTE(A3,",",""))After testing the first row, copy the formula down the rest of the column.
Use a fixed input cell
=VALUE(SUBSTITUTE(A2,",",""))Lock assumptions, targets, or thresholds with dollar signs when needed.
Use an Excel Table
=VALUE(SUBSTITUTE(A2,",",""))Tables make formulas more readable and resilient.
Make the formula report-safe
=IFERROR(VALUE(SUBSTITUTE(A2,",","")),"")Avoid visible errors in a final report when inputs are missing.
Use LET for clear logic
=LET(result,VALUE(SUBSTITUTE(A2,",","")),result)LET helps document the calculation inside the formula itself.
How to use it step by step
- Put your raw data in clear columns with headers such as Amount, Date, Status, Region, or Customer.
- Paste the starting formula into the first result cell, usually row 2.
- Replace sample references such as A2, B2, or $F$2:$F$100 with the cells in your own worksheet.
- Check the result on a few rows where you already know the expected answer.
- Copy the formula down only after the first row returns the right result.
Common mistakes and fixes
- The referenced range does not include all rows.
- The cell format makes the result look wrong even when the formula is correct.
- A fixed assumption cell was copied down without dollar signs.
- Test the formula on a few known rows before using it across a full report.
Beginner tips
- Use a small sample first so you can see exactly how the Convert Numbers Stored as Text formula behaves.
- Convert your source range into an Excel Table when the data will grow over time.
- Add a short note near important formulas so future users know what each input represents.