How to Convert Numbers Stored as Text to Numbers in Microsoft Excel

A frequent complication when importing financial reports, CSV files, or database exports into Microsoft Excel is numbers unexpectedly behaving as text strings. When Excel treats digits as text, standard mathematical operations, formulas such as SUM or AVERAGE, and sorting rules fail to compute properly. Often, Excel flags these cells with a small green error triangle in the top-left corner, and values align to the left side of the cell by default.

Whether you are dealing with a handful of problematic cells or thousands of rows imported from third-party accounting software, Excel provides multiple built-in methods to convert text-formatted digits back into true numerical values.

Method 1: Use the Error Checking Warning Icon

When Excel identifies numbers stored as text, its background error-checking rule automatically displays an alert icon next to the selection:

1. Select the range of cells containing the green error indicators.
2. Click the yellow diamond-shaped warning icon (Error Options) that appears adjacent to the top-right corner of the selected cells.
3. From the drop-down contextual menu, select Convert to Number.

Excel immediately converts all selected text entries into true numeric values and realigns them to the right-hand margin. If the warning icon does not appear, your error-checking rules may be disabled in File > Options > Formulas > Numbers formatted as text or preceded by an apostrophe.

Method 2: Convert Using the Paste Special Multiply Trick

For extensive worksheets where the error indicator does not appear or selecting large ranges causes lag, the Paste Special multiplication technique forces instant coercion:

1. Click into any empty, unformatted cell on your worksheet.
2. Type 1 and press Enter.
3. Select the cell containing 1 and press Ctrl + C to copy it to your clipboard.
4. Highlight the entire range of numbers currently stored as text.
5. Right-click anywhere within the highlighted range and choose Paste Special (or press Ctrl + Alt + V).
6. Under the Operation section, select the Multiply radio button.
7. Click OK.

Multiplying any number by 1 does not change its mathematical value, but it forces Excel to parse the cell’s underlying data type from text into a floating-point number. You can safely delete the temporary cell containing 1 once finished.

Method 3: Convert Using the Text to Columns Wizard

The Text to Columns tool is another swift method that re-evaluates an entire single column without altering existing formulas or formatting:

1. Highlight the single column containing the text-formatted numbers.
2. Navigate to the Data tab on the Excel ribbon.
3. In the Data Tools group, click Text to Columns.
4. In the wizard dialogue that appears, leave the default selection on Delimited and click Finish directly without progressing through the other steps.

By pressing Finish on Step 1, Excel immediately parses the raw string data through its standard input interpreter, successfully transforming text numbers into valid figures.

Method 4: Convert via the VALUE Formula

If you prefer non-destructive conversion that outputs clean numbers into an adjacent column while preserving your original raw import data:

1. Insert a blank helper column next to your text numbers.
2. Assuming your first text value is located in cell A2, enter the following formula into cell B2:

=VALUE(A2)

3. Press Enter and double-click the fill handle in the lower-right corner of cell B2 to populate the formula down the entire dataset.
4. To replace your original column with the newly converted values, copy column B, right-click cell A2, and select Paste Values (represented by the clipboard icon with numbers 123).