Keep Numbers as Text in Excel: A Step-by-Step Guide

To keep numbers as text in Excel, format the destination cells as Text before typing or pasting, or enter an apostrophe before a value. For data already in the sheet, use Text to Columns or a conversion formula. These methods are useful for product codes, ZIP or postal codes, phone numbers, account IDs, and other digit strings that must retain leading zeros or be treated as labels rather than quantities.

Excel normally interprets digit-only entries as numbers. That can remove leading zeros, display long values in scientific notation, or make Excel use the values in arithmetic. Text storage preserves the characters you enter, but text values do not behave like numbers in calculations. Choose the method that fits your task: prepare cells before entry when possible, or convert existing values when necessary.

What You’ll Need

An Excel workbook and the values you want to store as text.
If the data is already in the workbook, a spare column or a copy of the original data is helpful while converting it.
For IDs that need leading zeros restored, the intended number of digits or a reliable source of the original values. Formatting cannot restore zeros Excel has already discarded.

Step-by-Step

Decide whether the values are identifiers or quantities. Store values such as phone numbers, invoice codes, ZIP codes, and SKU numbers as text when you need to preserve their exact characters. Keep amounts, counts, dates, and measurements as numeric values if you will sort them numerically, add them, or use them in calculations. A number that happens to contain only digits is not necessarily a quantity.
Format blank cells as Text before typing. Select the cells or entire column where the entries will go. On the Windows ribbon, choose Home > Number Format (the drop-down that may show General) and select Text. Now type or paste the values. For example, entering 00427 into a cell formatted as Text keeps all five digits. Formatting the whole destination column first is especially useful for repeated data entry and for pasting a list.
Use an apostrophe for an occasional entry. Type an apostrophe immediately before the value, such as '00427, then press Enter. Excel treats the entry as text and does not show the leading apostrophe in the cell; it is an input marker, not part of the displayed value. This is quick for a few cells, but it is easy to overlook when entering a large list. If the apostrophe itself is part of the data in a file or another system, this method may not be suitable.
Convert existing entries with Text to Columns. Select the cells containing the values, then choose Data > Text to Columns. In the wizard, choose Delimited and click Next, leave the delimiter boxes clear if each selected cell already holds one complete value, and click Next again. Under Column data format, choose Text, check that the destination is where you want the results, and click Finish. This turns the selected entries into text. If the cells currently display 427 because Excel already removed the zeros from 00427, this operation cannot infer the missing zeros; use a known fixed width or recover the source data.
Convert with a formula when you want to keep the original column. Suppose the existing values are in A2:A20 and you want text versions in B2:B20. In B2, enter =A2&"", then fill the formula down. If A2 contains the numeric value 427, the result in B2 is the text value 427. Copy the new column and use Home > Paste > Paste Values to replace the formulas with fixed text, if needed. This does not bring back zeros that were already lost. Keep the original column until you have checked the converted results.
Import a CSV or text file with the column set to Text. On Windows, use Data > From Text/CSV and select the file. In the preview, choose Transform Data. In the Power Query Editor, select the relevant column, open its data type menu, choose Text, and confirm replacing the current type if prompted. Then choose Home > Close & Load. Review the loaded values, particularly IDs with leading zeros and long digit strings. If you simply double-click a CSV, Excel may automatically interpret its contents before you can control the types.
Verify the result before deleting or sharing anything. Check a few values at the start, middle, and end of the list. Confirm leading zeros remain, long values are complete, and the entries behave as text when selected or used in a test. If you used a helper formula, retain the source until the output is correct and saved. On Mac, the cell format and apostrophe approaches are available too; ribbon labels and the Text to Columns or import workflow can vary by Excel release. In Excel for the web, use the Home number-format menu for Text, and consider preparing the data in desktop Excel if you need the full import controls.

Common Mistakes

Applying Text format after typing. Changing a cell’s number format to Text does not automatically convert a value that Excel has already stored as a number. It may still show without its original zeros. Convert existing values with Text to Columns or a formula, or re-enter them after applying Text format.

Expecting a format to restore missing characters. If you entered 00123 in a General cell and Excel stored it as 123, the original zeros are no longer part of that value. A format can make zeros appear, but that is display formatting rather than the same thing as storing the original character string. Use the source file or a known code width to rebuild the text correctly.

Turning genuine numbers into text accidentally. Text values may not be included as expected in sums, numeric comparisons, or charts. Keep data that represents a measurable amount numeric. If a column mixes IDs and quantities, decide how each field should behave rather than formatting the entire worksheet as Text.

Opening a CSV by double-clicking it. Excel may automatically convert columns on opening, potentially removing leading zeros or changing long values. Import the file through Data > From Text/CSV and set the appropriate column to Text instead.

Tips

Use a dedicated text format for identifiers at the point where they enter the workbook. If coworkers paste into the same column, tell them to paste values into the prepared cells and check that the column remains Text. A data-entry template with the relevant columns already formatted can prevent repeated conversion work.

Text sorting is alphabetical rather than numeric. For example, text values may sort as 1, 10, 2, while numeric values sort as 1, 2, 10. That is usually fine for codes, but it matters if readers expect a numerical sequence. If an identifier has a fixed length and is made only of digits, a consistent length can make text sorting more orderly.

If a numeric source contains 427 and the required identifier has five digits, you can create a padded text value in a helper column. With the source value in A2, enter =TEXT(A2,"00000") in B2; for 427, the result is the text value 00427. The format pattern must match the required width. This is appropriate only when the code is known to have five digits; it should not be used to guess the length of unknown identifiers.

For long digit strings, text storage is particularly important because Excel supports only 15 digits of numeric precision. If a long account or tracking number has already been interpreted as numeric, digits beyond that precision may have changed, not merely been hidden. Import or format the column as Text before Excel processes the value, and compare it with the original source.

Troubleshooting

The leading zeros still disappear. First check whether you formatted the cells before entering or pasting the data. If you formatted them afterward, convert the existing values with Text to Columns or re-import the original file with that column typed as Text. If the stored value is already 427, use the original data or a verified fixed-width rule to determine whether it should be 00427. Do not assume all codes have the same length unless the source confirms it.

The formula result is still a formula. If you see the formula text rather than a result, the destination cell may be formatted as Text, or Excel may be in Show Formulas mode. Change the formula cell to General, re-enter the formula, and press Enter. On Windows, Ctrl+` toggles Show Formulas; on Mac, the shortcut may vary by keyboard and Excel version, so use Formulas > Show Formulas if available. Afterward, check that the formula references the intended source row.

Values appear left-aligned or show a warning icon. Excel commonly displays text left-aligned and may flag a number stored as text. For an identifier, that is often the intended result; you can leave it as text. Do not choose a convert-to-number option if doing so would remove zeros, change a long code, or make Excel treat an ID as a quantity.

Text to Columns changes more than expected. Undo immediately with Ctrl+Z on Windows or Command+Z on Mac, then repeat on a copy or a narrower selection. In the wizard, confirm that the selection covers the intended cells, do not select a delimiter that splits the content, and set the column data format to Text before finishing. For a file import, return to the preview or Power Query Editor and verify the column type before loading.

A long number has changed into E+ notation or different digits. Scientific notation can be only a display choice for shorter numeric values, but Excel’s 15-digit precision limit can permanently alter longer values stored as numbers. Re-import the original source with the column set to Text. Widening the column may reveal more of a value, but it cannot recover digits that Excel already changed.

Frequently Asked Questions

Does changing the cell format to Text convert a number already in Excel?
Not reliably. Text format controls how new entries are interpreted, but an existing numeric value usually stays numeric until you convert or re-enter it. Use Text to Columns or a helper formula for existing values, then verify the result.

How do I keep leading zeros in Excel?
Format the destination cells as Text before typing or pasting, use an apostrophe before an individual value, or import the source column as Text. If Excel already removed the zeros, retrieve the original value or apply a confirmed fixed-width rule.

Can numbers stored as text be used in calculations?
They are not numeric values, so calculations may ignore them or require conversion. Keep a separate numeric column for arithmetic when appropriate, and retain text for the identifier. Avoid converting codes to numbers if their exact characters matter.

Will text formatting preserve a 20-digit number?
Yes, when Excel receives it as text from the start, such as through a preformatted cell or a correctly configured import. If Excel first stores it as a number, its 15-digit precision limit can change later digits. Re-import the original source as Text if that has happened.

Why does Excel show a green triangle next to my value?
It may be warning that a number is stored as text. For codes and identifiers, this is often intentional, so you can ignore the warning. Use the error-checking menu only if you have confirmed that the value should instead be numeric.

Can I use this method in Excel for the web?
Yes. You can select cells and choose Text from the Home tab’s number-format menu, and you can enter an apostrophe before an individual value. For more controlled CSV imports or conversion steps, desktop Excel may offer additional options depending on your version.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.