How to Convert Text to a Number in Excel

Excel is powerful, but it has one quirk that trips up nearly every user at some point: numbers that look like numbers but behave like text. They won't sum, won't sort correctly, and formulas that reference them return zero or an error. Understanding why this happens — and the several ways to fix it — puts you back in control of your data.

Why Excel Stores Numbers as Text

When you import data from a CSV, copy from a web page, pull from a database, or receive a file from another system, Excel doesn't always interpret values the way you'd expect. Numbers may arrive with leading apostrophes, extra spaces, non-standard formatting, or simply flagged as text by the source system.

You can spot text-formatted numbers by a few clues:

• A small green triangle appears in the top-left corner of the cell
• Numbers left-align instead of right-aligning (Excel's default for numeric values)
• SUM() or COUNT() returns 0 or an unexpected result
• The cell shows a "Number Stored as Text" warning in the error indicator

The problem isn't cosmetic. Text values are excluded from arithmetic operations, pivot table calculations, and many lookup functions. Fixing the format is essential before doing any meaningful analysis.

Method 1: Use the Error Indicator (Quickest Fix)

If Excel has already flagged your cells with the green triangle warning, this is the fastest route.

1. Select the affected cells
2. Click the yellow warning diamond that appears near the selection
3. Choose "Convert to Number" from the dropdown

This works well for small datasets where Excel has already identified the issue. It won't always appear, though — particularly if the text format was applied intentionally or came from certain import sources.

Method 2: Multiply by 1 (Formula Approach)

One of the most reliable methods is forcing a math operation on the text value. In an empty column, enter:

=A1*1 

Or equivalently:

=A1+0 

Excel performs the arithmetic, which coerces the text into a number. The result is a true numeric value that functions, formulas, and pivot tables will recognize. You can then copy and paste as values back into the original column if needed.

Method 3: The VALUE() Function

The VALUE() function is purpose-built for this conversion:

=VALUE(A1) 

It converts a text string that represents a number into an actual numeric value. It's particularly useful when:

• Numbers have consistent formatting (e.g., all plain digits)
• You want a clean, readable formula in your sheet
• You're combining the conversion with other functions

VALUE() will return a #VALUE! error if the cell contains characters that can't be interpreted as a number — which is actually helpful, because it flags data quality problems you'd want to investigate anyway.

Method 4: Paste Special — Multiply

This approach converts values in place without needing a helper column:

1. Type 1 in any empty cell and copy it
2. Select the range of text-formatted numbers
3. Right-click → Paste Special
4. Choose Multiply under the Operation section → click OK

Excel multiplies each selected cell by 1, which converts text to numbers directly. The original values stay in place — no extra columns, no formulas to clean up afterward.

Method 5: Text to Columns

The Text to Columns wizard isn't just for splitting data — it's a surprisingly effective conversion tool:

1. Select the column of text-formatted numbers
2. Go to Data → Text to Columns
3. Click Finish immediately (no need to change any settings)

Excel re-parses the column and often resolves the text-number conflict automatically. This works especially well after importing data from external sources.

Method 6: TRIM() and CLEAN() for Hidden Characters

Sometimes the issue isn't the number format — it's invisible characters like extra spaces, line breaks, or non-printing characters embedded in the cell.

=VALUE(TRIM(CLEAN(A1))) 

• CLEAN() removes non-printing characters
• TRIM() removes leading, trailing, and excess internal spaces
• VALUE() then converts the cleaned string to a number

This combination handles messy imported data that other methods may fail on.

Comparing the Methods 📊

Variables That Affect Which Method Works

Not every method works in every situation. What determines the right approach:

• Data source — Imported CSVs, database exports, and web scrapes introduce different kinds of text artifacts
• Volume — A few dozen cells suits the error indicator; thousands of rows call for a formula or Paste Special approach
• Data structure — Are the numbers formatted consistently, or is there mixed content in the column?
• Excel version — Behavior of the green triangle and certain import tools varies slightly between Excel 2016, 2019, Microsoft 365, and the web version
• Locale settings — Decimal separators (period vs. comma) can cause VALUE() to fail if your system locale doesn't match the data's format
• Whether you need to preserve formulas — Paste Special and Text to Columns overwrite cells; helper columns keep your original data intact

When the Conversion Doesn't Stick 🔧

Some users find their numbers revert to text after saving or refreshing a data connection. This usually means the source of the data — a Power Query connection, an external database link, or a recurring import — is re-applying the text format on each load. In those cases, the fix belongs upstream: in the Power Query transform steps, the import settings, or the data type definitions in the source system.

The method that works best for a standalone spreadsheet may not be the right answer for a file that refreshes from live data. What's actually driving the text format in your specific file — and whether that source resets on refresh — changes the solution entirely.