Clean data
Convert Numbers Stored as Text to Real Numbers in Excel
Find numbers stored as text in Excel and choose a conversion method that protects IDs, formulas, and number formats.
Numbers stored as text may look like ordinary amounts, yet they can fail to sum, sort unexpectedly, or break a lookup. The right fix depends on whether the column is truly numeric. Account numbers, ZIP codes, and SKUs should often remain text.
Confirm the problem first
Excel may show a warning indicator for a number stored as text. ISNUMBER(A2) returns FALSE for text, even when the cell displays digits. Compare a few cells from the top, middle, and bottom of the column; exports sometimes mix real numbers with text.
Choose a conversion method
| Method | Useful when | Check before using it |
|---|---|---|
| Error menu → Convert to Number | A small selection has Excel's warning | The selected cells are amounts, not IDs |
| Paste Special → Multiply | Many simple numeric strings need conversion | The decimal convention is consistent |
VALUE or NUMBERVALUE | You want a helper column and a review step | Choose the right decimal and grouping separators |
| Text to Columns | A whole imported column needs a type choice | Do not let Excel turn codes into dates or remove zeroes |
| Power Query | The same export recurs | Set the type deliberately and inspect errors |
Convert a small selection with Excel's warning menu
Select only the amount cells that need conversion, open the warning icon and choose Convert to Number. Test =ISNUMBER(A2) again and compare the total before repeating the change across the column. Applying a Number display format alone does not convert a text value.
Use a helper column for a reviewable conversion
Enter =VALUE(A2) in a separate column for values that use your Excel locale. For an explicitly decimal-comma export, =NUMBERVALUE(A2,",",".") makes the decimal and grouping rules visible. Fill down, inspect conversion errors and reconcile the sum. Copy the reviewed helper column and paste values into an intended input column only after keeping an original copy.
Convert a regular imported column
For simple numeric strings, type 1 in an empty cell, copy it, select the intended amount cells and use Paste Special → Multiply. Avoid applying that change over formulas or identifiers. With Text to Columns, select the column, choose Delimited without a separator when you are only converting types, and inspect the destination and General/Text choices before finishing. For recurring imports, Power Query's Change Type → Using Locale records the conversion step and exposes errors for review.
Watch the locale and formatting
1,234.56 and 1.234,56 use different decimal conventions. A blanket conversion can produce a number with the wrong magnitude. Currency symbols, percent signs, parentheses for negative amounts, and non-breaking spaces can also require a deliberate parse. After conversion, apply the appropriate Excel number format and verify a sample total.
Keep identifiers as text
The string 00123 may be a distinct customer account from 123. Converting it to a number erases the leading zeroes and can create a false match. When your actual goal is to connect two sheets by an ID, inspect both key columns before using Match & Merge.
Common questions
Will conversion change my formulas?
Changing referenced values may change formula results even if the formula cells remain in place. Make a copy, convert a sample, and check dependent totals before changing the full column.
Can I do this with a CSV?
Yes, but CSV has no saved cell types or formats. Choose import types deliberately, especially for IDs and dates.
Use the Excel conversion methods above for this task. TidyCell does not currently provide a dedicated numbers-as-text converter.
Ask for help with a stuck conversion
Describe the value, Excel's result and the formula you tried. Ask TidyCell can help you decide which conversion method to check.
Open Ask TidyCell