To convert text to a number in Excel, select the cells, click the yellow warning icon that appears beside them, and choose Convert to Number. Excel changes the text into real numbers you can add and sort. For a whole column, the fastest fix is Data, Text to Columns, then click Finish without changing anything, which re-reads every value as a number. If you prefer a formula, =VALUE(A2) converts one cell and fills down. Any of these turns left aligned text into proper numbers that SUM will actually add.
This problem is everywhere after a PDF conversion. Amounts pulled out of a converted or scanned document often land as text, stored with a leading apostrophe or an invisible character, so they sit left aligned in the cell and formulas ignore them. Here is how to spot text numbers and every reliable way to convert them back.
How do you convert text to a number in Excel?
Select the range of text numbers. A small green triangle sits in the top left corner of each cell, and selecting them brings up a yellow diamond warning icon. Click it and pick Convert to Number from the menu. Excel switches the whole selection to real numbers in one step, they jump to right aligned, and the green triangles disappear. This is the quickest method for a block of cells and it does not need a helper column or a formula. If the warning icon does not appear, the values may have a hidden character, which the sections below cover.
How do you convert an entire column of text to numbers?
Use Text to Columns. Select the column, go to Data, click Text to Columns, choose Delimited, click Next, leave every delimiter unticked, click Next again, and click Finish. Excel re-parses each value and writes it back as a number, converting the whole column at once. Another whole column method is Paste Special: type the number 1 in a blank cell, copy it, select your text numbers, then Paste Special and choose Multiply. Multiplying by 1 forces Excel to re-evaluate each cell as a number without changing its value. Both handle thousands of rows in one action.
How do you convert text to a number with a formula?
The VALUE function does it directly: =VALUE(A2) returns the numeric version of the text in A2, and you fill it down the column. A shorter trick is the double unary, =--A2, where the two minus signs flip the text to a number twice and leave it positive. Both build the numbers in a new column, so once they calculate you copy the results and Paste Special as Values over the original text. Use a formula when the text is arriving continuously and you want the conversion to update on its own.
How can you tell if a number is stored as text?
Three signs. First, text numbers align to the left of the cell by default while real numbers align right. Second, a green triangle appears in the corner, Excel's flag for a number kept as text. Third, and most reliable, test a cell with =ISNUMBER(A2), which returns FALSE for text and TRUE for a genuine number. ISTEXT does the opposite. If a column will not add up in a SUM, running ISNUMBER down it quickly shows which values are the problem.
Why won't Excel let me convert text to a number?
When Convert to Number does nothing, a hidden character is usually stuck to the value. The common offender is a non breaking space, character code 160, which often rides along from copied or converted data and is not removed by a normal trim. Clean it with =VALUE(SUBSTITUTE(A2,CHAR(160),"")), or nest TRIM and CLEAN inside VALUE to strip regular spaces and control characters too. A leading apostrophe, which forces text, is removed by Text to Columns or by the Convert to Number menu. Once the stray character is gone, the value converts normally.
How do you stop Excel showing a number as scientific notation?
Long numbers like a 16 digit account or a long invoice ID often display as 1.23E+15 once Excel treats them as a number, and worse, Excel only keeps 15 significant digits, so everything past the fifteenth turns to zero and the value is corrupted for good. If the ID is a label you never do math on, keep it as text on purpose: format the column as Text before you paste, or type a leading apostrophe. Only convert to a real number the values you actually need to add. This is the one case where numbers stored as text is the right answer, not a bug.
Which text to number method should you use?
| Method | Best for |
|---|---|
| Convert to Number (warning icon) | A small block of cells, fastest |
| Text to Columns, Finish | A whole column in one step |
| Paste Special, Multiply | A whole column, no wizard |
| =VALUE(A2) | A formula that updates live |
| SUBSTITUTE with CHAR(160) | Values with a hidden space |
Numbers imported as text after a PDF conversion
This is the single most common complaint after converting a financial document. A converter reads the digits but tags them as text, so your amounts will not sum and a total reads zero. It happens most with scanned pages that go through OCR and with figures that carried a currency symbol or thousands separator in the original. Fixing it is exactly the steps above, and we cover the conversion specific angle in why a PDF converts to Excel with numbers as text. The deeper fix is upstream: a PDF to Excel converter that outputs real numbers means no cleanup at all, and keeping the columns intact is the point of a conversion that preserves formatting. The same text number problem shows up when pulling figures off a bank statement, which is why a tool built for that job returns numbers ready to add.
The short version
Select the cells and choose Convert to Number from the warning icon for a quick fix, or use Text to Columns and click Finish to convert a whole column. VALUE and the double unary do it with a formula, and a stubborn cell usually has a non breaking space that SUBSTITUTE with CHAR(160) removes. Check your work with ISNUMBER. If the text numbers came from a converted PDF, a converter that returns real numbers saves you the whole exercise.