You see numbers in your Excel sheet that are left-aligned like text. This prevents you from using them in formulas or charts. The cells are storing the numbers as text strings, which is a common data import error. This article explains why this happens and provides steps to convert the text back to real numbers.
Key Takeaways: Fixing Numbers Stored as Text
- Error Checking green triangle: Use the warning icon next to the cell to convert one or multiple text entries to numbers instantly.
- Paste Special > Multiply: Convert an entire column of text-numbers by multiplying the values by 1 using a blank cell.
- Data > Text to Columns: Force a conversion of selected cells to the General number format in the final step of the wizard.
Why Excel Stores Numbers as Text
Excel aligns true numbers to the right by default and text to the left. When a number is left-aligned, it is almost always a text string that looks like a number. This occurs because the data came from an external source like a database, web page, or text file. These sources often include invisible formatting characters or leading apostrophes that tell Excel to treat the entry as text.
Another common cause is opening a CSV file. Excel reads the file and may interpret certain number formats, especially those with leading zeros or special delimiters, as text to preserve their appearance. Formulas like SUM or AVERAGE will ignore these text-numbers, causing calculation errors. The status bar may show a count but not a sum for the selected cells.
Steps to Convert Text to Numbers
Use one of these methods to change text-numbers into values Excel can calculate. The best method depends on whether you see the green error-checking triangle and how much data you need to fix.
Method 1: Use the Error Checking Warning
This is the fastest method if your cells display a small green triangle in the top-left corner.
- Select the problem cell or range
Click on a single cell with a green triangle or drag to select multiple cells. - Click the warning icon
A yellow diamond with an exclamation point will appear next to the selected cell or range. Click on it. - Choose Convert to Number
Select this option from the drop-down menu. The cell alignment will immediately change to right-aligned.
Method 2: Use Paste Special to Multiply by 1
This method works on any range of text-numbers, even without the green error indicator.
- Enter the number 1 in a blank cell
Type 1 in any empty cell and press Enter. Then copy that cell by pressing Ctrl+C. - Select your text-number cells
Highlight all the cells containing left-aligned numbers you want to fix. - Open Paste Special
Right-click on the selected range and choose Paste Special from the context menu. - Select Multiply and click OK
In the Paste Special dialog box, under the Operation section, select Multiply. Click OK. This multiplies all selected values by 1, forcing a conversion to numbers. - Clear the copied cell
You can now delete the cell where you typed the number 1.
Method 3: Use the Text to Columns Wizard
This tool is reliable for fixing numbers imported from other systems, especially in a single column.
- Select the column of data
Click the column header letter to select the entire column of text-numbers. - Go to Data > Text to Columns
On the Data tab of the ribbon, click the Text to Columns button. - Click Finish in the wizard
In the Convert Text to Columns Wizard, simply click Finish on the very first step. The default settings will convert the text to the General number format.
If Standard Conversion Methods Do Not Work
Sometimes numbers remain as text because of hidden characters or stubborn formatting. Try these specific fixes.
Cells Contain Extra Spaces or Non-Breaking Spaces
Spaces before or after the number will keep it as text. Use the TRIM function. In a blank column next to your data, type =TRIM(A1) assuming A1 is your first text-number. Press Enter, copy the formula down, then copy the results and use Paste Special > Values over the original column.
Numbers Have Leading Apostrophes
An apostrophe before a number forces text format and is invisible in the cell. To find them, select the column and press Ctrl+F to open Find. In the Find what field, type an apostrophe (‘). Click Find All. If any are found, you need to remove them. The Text to Columns wizard (Method 3) typically removes these apostrophes during conversion.
Cell Format is Stuck as Text
Even after conversion, if you type new numbers they may revert to text. Change the cell format first. Select the cells, go to Home > Number Format box, and choose General or Number. Then re-enter the values or use any conversion method again.
Error Checking vs. Paste Special vs. Text to Columns
| Item | Error Checking | Paste Special Multiply | Text to Columns |
|---|---|---|---|
| Best For | Individual cells or small ranges with the green triangle | Large ranges without the error indicator | Entire columns of data from external sources |
| Speed | Fastest for few cells | Fast for large blocks | Fast for single-column data |
| Removes Apostrophes | Yes | Yes | Yes |
| Removes Extra Spaces | No | No | No |
| Changes Cell Format | Converts to General format | Converts to General format | Converts to General format |
You can now identify and fix cells that store numbers as text. Use the Error Checking method for quick fixes on marked cells. For bulk conversion without the green triangle, the Paste Special trick is most reliable. To prevent this issue when importing data, use the Data > From Text/CSV import wizard which provides better type detection. For advanced control, use the VALUE function in a helper column to convert text to numbers within a formula.