A number stored as text is a cell whose contents read as digits but whose type, as far as Excel is concerned, is a string. The fastest fix, in Excel for Windows: select the cells, click the error button that appears at the top left of the selection, and choose Convert to Number. The reason it is worth a whole page is the failure mode. Microsoft’s SUM documentation states it plainly: “SUM will ignore text values and give you the sum of just the numeric values.” A total over a column with two text entries in it is not an error, it is simply a smaller number, and nothing at the total says so.
That is the quiet-corruption pattern this page is about: how to spot text-numbers, the documented ways to convert them, and the cases where text is the correct type, because leading zeros vanish from numbers and 16-digit codes lose digits.
How to tell if a number is stored as text
| A | B | |
|---|---|---|
| 1 | Invoice | Amount |
| 2 | 101 | 1,200 |
| 3 | 102 | 850 |
| 4 | 103 | 45 |
| 5 | 104 | 80 |
| 6 | Total | 2,050 |
Excel gives you several tells, none of them loud.
- The error indicator. Excel often marks text-numbers with an error indicator, the small green triangle it puts on the cell. Select a flagged cell and an error button appears next to it with the fix in its menu.
- Left alignment. Numbers formatted as text sit left-aligned in the cell instead of right-aligned, Excel’s default for real numbers. It is the quickest visual check, though it only works where nobody has overridden the alignment by hand.
- An ISTEXT check. Point it at a suspect cell:
TRUE means the cell holds text, whatever it looks like. Its mirror, =ISNUMBER(B4), confirms a real number. These functions do not convert their argument first, so they report the cell’s actual type.
- A COUNT versus COUNTA comparison.
=COUNT(B2:B5)counts only the numeric values in the range, while=COUNTA(B2:B5)counts every cell that contains anything. On a column that should be all numbers, a COUNT that comes up short tells you how many text entries the range holds. The status bar at the bottom of the window can run the same check without a formula: right-click it to show both Count and Numerical Count for the current selection, and if the two disagree on an all-numbers column, some of those cells are not numbers. - A total that reads low. The grid above is the whole symptom: the SUM gives no error, it is just smaller than it should be. If a checksum row or a hand total disagrees with SUM, suspect types before arithmetic.
Why Excel stores numbers as text
Three causes account for nearly all of it.
Imported or pasted data. Microsoft’s guidance on text-formatted numbers notes the issue sometimes occurs after you import or copy data from a database or other external data source. CSV exports, reports pasted from a browser, and system extracts are the classic carriers: the digits arrive, the type does not.
The cell was formatted as Text before the value was typed. The Text number format tells Excel to store whatever is entered as entered. Apply it to a column, type numbers into it, and every one of them is a string. The same mechanism also catches formulas, which then display themselves instead of calculating; that sibling symptom has its own walkthrough.
A leading apostrophe. Typing an apostrophe in front of a number tells Excel to treat it as text. That is a deliberate feature, and later in this page it is the right tool. It only becomes a bug when the apostrophes arrive by accident, riding in with pasted data.
How to convert text to numbers
Three routes get you there: the error button’s Convert to Number, a VALUE helper column, and Paste Special’s multiply trick. Microsoft’s current support page documents the first two, and an older page it still hosts documents the third. All three end in the same place: the cell’s stored contents become a real number.
The error button: Convert to Number
In Excel for Windows, select the cells you want to convert, then click the error indicator at the top left of the selection, or press Alt+Shift+F10 to open the same menu from the keyboard. Choose Convert to Number and the green triangle warnings go with it. This is the fix for the common case: a block of imported values you can select in one drag.
If no error button appears, background error checking may be off. In Excel for Windows it lives under File > Options > Formulas, where Enable background error checking switches the indicators back on.
The VALUE function
Where the button is not available, including Excel for the web, the documented route is a helper column. VALUE converts a text string that represents a number into that number:
Insert a new column beside the text numbers, point =VALUE() at the first one, and fill it down by dragging the handle at the cell’s lower right corner. Then either use the new column directly or copy it and paste it over the original column as values (Paste Special > Values, or Ctrl+Shift+V), and delete the helper.
Paste Special, multiply by 1
An older Microsoft page documents a trick that converts in place without a helper column. Type 1 into a blank cell whose format is General and copy it. Select the cells holding text numbers, open Home > Paste > Paste Special, choose Multiply under Operation, and click OK. Multiplying by 1 leaves every value looking identical, but the arithmetic forces Excel to replace each cell’s text contents with the numeric equivalent. Delete the cell with the 1 when you are done.
Why setting the format to General is not enough
The instinctive fix, select the column and switch its format from Text to General, changes nothing you can see, because a format change alone does not re-enter what the cells already store. Microsoft’s fix for the formula version of this problem makes the point: set the format to General, then press F2 and Enter, re-entering the contents under the new format. The same rule cuts the other way too: Microsoft’s leading-zeros guidance notes that applying the Text format does not change numbers already entered, only entries made after the format is applied. The format decides how an entry is stored at the moment it is entered. For anything already in the grid, use one of the three conversions above, which exist precisely so you do not have to retype a thousand cells.
When text is the right answer: keeping leading zeros
Some digits are codes, not quantities. Zip codes, phone numbers, part numbers, and account codes are the usual cases, and Excel’s defaults fight you on them: per Microsoft, “Excel automatically removes leading zeros, and converts large numbers to scientific notation, like 1.23E+15, in order to allow formulas and math operations to work on them.”
There are two documented ways to keep the zeros, and they differ in what the cell ends up holding.
A custom number format keeps the value numeric. Select the cells, press Ctrl+1 to open Format Cells, and on the Number tab choose Custom. A format of 00000 pads every entry to five digits for a postal code; Microsoft’s own example is 000-00-0000 for a social security number.
| A | B | |
|---|---|---|
| 1 | Office | Postal code |
| 2 | Holtsville | 00501 |
| 3 | Adjuntas | 00601 |
| 4 | Boston | 02110 |
The Text format, or an apostrophe, keeps the zeros as text. Format the cells as Text before entering the data (Ctrl+1, Number tab, Text), or type an apostrophe in front of an individual entry, and Excel stores the characters exactly as typed. Remember the caveat from the previous section: the Text format only governs entries made after it is applied. For imported files, the same choice exists at the door; in the Get & Transform (Power Query) import experience you can set a column’s data type to Text before the data ever lands in the grid, which is the reliable way to bring in zip-code columns from a CSV.
Which to pick? If the codes ever feed arithmetic or numeric lookups, the custom format wins, because the underlying values stay numbers. If the codes are identifiers that must round-trip exactly, text is honest about what they are. Just make the choice deliberately, per column, so it does not get made for you cell by cell.
Long numbers, scientific notation, and the 15-digit limit
Two separate things happen to long numbers, and only one of them is cosmetic.
The cosmetic one: in the General format, Excel displays large numbers of 12 or more digits in exponential notation, so a perfectly healthy 12-digit account number shows up in 1.23E+11 style instead of as its digits. Applying a different number format, such as Number, brings the digits back, and per Microsoft, changing the number format leaves the value Excel actually calculates with untouched. If your long number has 15 digits or fewer, this is purely a display problem with a display fix.
The destructive one: Excel’s calculation precision is 15 significant digits. Type a number with 16 or more digits, a credit card number being the standard example, and every digit past the 15th is rounded down to zero. Microsoft is unambiguous that number codes of 16 digits or more must use a text format.
The order of operations is the trap. Formatting the cell as Text after typing does not help, because the trailing digits became zeros at entry, and a format change does not alter the stored value. The cells have to carry the Text format, or the entries a leading apostrophe, before the long codes go in. This is also why a scan of an unfamiliar workbook treats long-number columns with suspicion: a column of card numbers or reference codes stored as numbers has already lost data, silently, in a way no amount of reformatting reveals.
What breaks downstream
A handful of text-numbers radiates failures that surface far from the cells themselves.
- Totals come out low. SUM adds the numeric values and ignores the text ones, as in the grid at the top of this page. The same applies to the conditional versions: SUMIF’s documentation has it ignoring blank cells and text values in the ranges it works on, so a criteria-based total silently drops them too, covered in the COUNTIF and SUMIF guide.
- Lookups miss. A VLOOKUP or XLOOKUP searching for the number 1005 will not match a cell holding the text 1005. The lookup returns an error and the type mismatch is one of the top causes, walked through in why your VLOOKUP returns an error.
- Sorts scramble. Microsoft notes text-formatted numbers can produce confusing sort orders, because text sorts character by character rather than by magnitude.
- Plus-sign arithmetic disagrees with SUM.
=B2+B3+B4+B5converts clean text numbers on the fly, the same coercion the Paste Special multiply trick relies on, so on the grid at the top of this page it returns the full 2,175 while=SUM(B2:B5)skips the text and returns 2,050. Text that is not a clean number, such as an entry carrying a stray space, makes the plus version return the#VALUE!error instead. When two formulas that should agree do not, the disagreement is itself a useful tell.
The short version
- A number stored as text looks fine and calculates as nothing: SUM ignores it, lookups miss it, a percentage built on it will not total, and no error appears. Suspect imported and pasted data first.
- Spot them by the green error indicator, left alignment,
=ISTEXT(), or a COUNT that disagrees with COUNTA on a column that should be all numbers. - Convert them with the error button’s Convert to Number, a
=VALUE()helper column pasted back as values, or Paste Special’s Multiply trick. Switching the format to General alone converts nothing that is already stored. - Keep leading zeros with a custom format like
00000when the values should stay numeric, or the Text format and apostrophes when the codes are identifiers. - Excel keeps 15 significant digits. Codes of 16 digits or more, card numbers included, must be entered as text; reformatting afterwards is too late.
- Scientific notation in a General cell is a display choice for numbers of 12 or more digits; apply the Number format to see the digits again.
The type of a cell is invisible in a printout and in most reviews, which is what makes this failure quiet. When a file that used to total correctly comes back from a re-import or another team and reads low, compare the two versions: the comparison flags a cell that changed from a number to text as its own finding, so the corrupted cells name themselves instead of hiding among thousands of unchanged ones.
