Excel’s Text number format tells Excel to treat entries in selected cells as text instead of interpreting them as numbers, dates, or formulas. To use it, select the cells and choose Home > Number Format > Text before entering data; for a custom format, the at sign (@) is the text placeholder.
What Excel’s Text format does
Excel normally tries to recognize what you type. An entry such as 125 may become a number, while 3/4 may be interpreted as a date according to your regional settings. This behavior is useful for calculations, but it can be wrong for values that only look numeric and are meant to identify something: account codes, product numbers, postal codes, or phone numbers.
When a cell is set to Text before you enter a value, Excel treats the entry as text. That helps preserve characters such as leading zeroes and can prevent number-like entries from being converted into dates or scientific notation. Text values are generally left-aligned by default, while numbers are generally right-aligned, although alignment can be changed independently.
Text formatting is not the same as changing a number’s appearance. A number format usually changes how an underlying numeric value is displayed while leaving that value available for calculations. Text format affects how Excel interprets entries made after the format is applied. That timing difference explains many surprises.
How to set a cell to Text
In Excel for Microsoft 365 on Windows, select the cells first, then use Home > Number Format (the dropdown in the Number group) > Text. You can also open Format Cells with Ctrl+1, choose the Number tab, select Text in the Category list, and choose OK. On Mac, Command+1 opens Format Cells; the Home tab also has a number-format dropdown.
- Select the empty cell, column, or range that will hold the text values.
- Choose Text using one of the paths above.
- Enter or paste the values, then check a few cells to confirm they are preserved as intended.
Applying Text format to a blank range in advance is the key step. If you type 00127 into a General-formatted cell, Excel may store the value as the number 127 and display it without the first zero. Changing that already-filled cell to Text afterward does not restore the lost zero or convert the stored number into the original character sequence. For existing values, use a conversion method or re-import the source data with the right column type.
Text format, the @ symbol, and custom formats
Excel has a built-in Text category in the Format Cells dialog. Separately, custom number formats use @ as a placeholder for text. You can create a custom format through Format Cells > Number > Custom, then enter a format code containing @. In a format code with sections separated by semicolons, the fourth section is the text section; @ marks where the text value appears. This is useful when you need a custom display for text, but it is not a conversion tool.
For example, a custom code such as "ID: "@ can display a text entry like A42 with an ID: prefix while keeping the underlying cell value as A42. By contrast, simply applying a custom format to a numeric value does not turn that value into text. Also, a custom numeric format such as 00000 pads a number with displayed zeroes but keeps it numeric. Choose the built-in Text format when the entry itself should be text; choose a custom number format when you want a number to remain numeric but look a particular way.
Keeping leading zeroes and identifiers
Identifiers often contain digits but do not represent quantities. You would not add two postal codes or calculate the average of product IDs, so storing them as text is usually appropriate. Before entering or importing such values, format the destination column as Text. Another quick option for a single entry is to type an apostrophe before it, such as '00127. Excel uses the apostrophe as an instruction to store the following characters as text; it does not appear as part of the cell’s displayed value.
If the values always have a fixed length and are genuinely numbers that you may calculate with, a custom number format can be preferable. For example, a five-digit code can be displayed with leading zeroes using 00000. The stored value remains numeric, so calculations still work. This display-only approach has a limitation: copying the value into a system that ignores Excel formatting may yield 127 rather than 00127. Text is safer when the zeroes are part of the identifier itself and must travel with the value.
- Use Text for codes where every character matters, including leading zeroes.
- Use a custom numeric format when you need a fixed-width display and the underlying value should remain numeric.
- For a one-off entry, use a leading apostrophe if changing the whole column is inconvenient.
What happens to existing values and formulas
Changing a cell’s number format changes its format setting; it does not automatically rewrite the existing cell value. If a cell contains numeric 127, choosing Text afterward does not make Excel recover a leading zero that was never stored. Likewise, if a cell displays a date because Excel interpreted an entry as a date, switching its format to Text may show a serial number or another representation of the existing value, rather than the original characters typed.
Formulas need special care. A formula entered into a Text-formatted cell may be displayed as formula text instead of being calculated. Set the cell back to General before entering the formula, then press F2 and Enter if necessary to make Excel re-evaluate an existing formula-like entry. On Windows, F2 edits the selected cell; on Mac, the equivalent may depend on the keyboard’s function-key settings, and editing the formula bar is another option. If the cell shows the formula rather than its result, also check that Show Formulas is not turned on.
To turn numbers stored as text into numeric values, select the affected cells and use the warning icon’s Convert to Number command when Excel offers it, or use an appropriate conversion method such as Text to Columns. Conversion can remove meaningful leading zeroes, so confirm the data’s purpose first. A text-formatted ID should generally stay text even if Excel flags it as a number stored as text.
Text format versus the TEXT function
Cell formatting and the TEXT function solve different problems. A number format affects how a value appears in its cell; the TEXT function returns a text string based on a number and a format code. That result can be useful when combining formatted values with other text, but it is text, not a number for ordinary arithmetic.
With the number 123 in A2, the formula =TEXT(A2,"00000") returns the text value 00123. The cell layout assumed here is A2 containing numeric 123; the result has five displayed digits because the format code supplies zeroes to the left. Use the function when another formula needs a formatted string. If you only want A2 to display five digits while retaining its numeric value, apply the custom number format 00000 instead.
Formatting does not add characters to the stored number, so functions that inspect the underlying value may behave differently from what the cell display suggests. In contrast, the result of TEXT is a string and may need conversion before it can be used in numeric calculations.
Importing data and common problems
Data imported from CSV files or copied from other applications can be interpreted differently from data typed into a preformatted range. A CSV file does not preserve Excel cell formats; when opened, Excel may infer dates, numbers, or scientific notation from the text. For more control in Microsoft 365, use Data > From Text/CSV to inspect the preview and set the relevant column’s data type to Text before loading it. The exact available import options can vary with the source and Excel version.
When pasting, the source formatting and the paste operation can affect the destination, so do not assume that a Text-formatted column guarantees every pasted value will remain as intended. After a paste or import, check examples with leading zeroes, long digit strings, and date-like entries. If Excel has already changed a value, merely changing its format may not restore the original characters; re-importing from the unaltered source is often the most reliable remedy.
- Leading zeroes disappear: format blank destination cells as Text before entry, or re-import the source column as text.
- A long code appears in scientific notation: use Text before entry or import; changing the display format afterward may not recover digits already rounded or altered.
- A formula appears instead of a result: change the cell to General, then re-enter or re-evaluate the formula.
- Numbers cannot be added: the cells may contain text. Convert only values that are truly quantities, not identifiers.
When Text is the right choice
Choose Text when a value is a label or code rather than a quantity, when its exact characters must be preserved, or when Excel’s automatic date and number recognition would be harmful. Common examples include ZIP or postal codes, phone numbers, SKU values, employee IDs, and digit strings longer than Excel can safely retain as numeric precision. Excel’s numeric precision is limited to 15 significant digits, so long identifiers should be stored as text from the outset if every digit must remain exact.
Keep values numeric when you need arithmetic, sorting by numerical size, or numeric comparisons. Text sorting is generally alphabetical, so codes such as 2 and 10 may not sort in the same order as numeric 2 and 10. If a code needs both exact preservation and a particular sort order, decide whether a separate numeric helper field is appropriate rather than converting the identifier itself without considering downstream uses.
Frequently Asked Questions
How do I make Excel treat a number as text?
Select the empty cells, choose Home > Number Format > Text, and then enter the values. For a single entry, type an apostrophe before the value. Formatting a number after it has been entered does not necessarily convert it or restore characters Excel removed.
What does @ mean in an Excel number format?
In a custom number format, @ is the placeholder for text. It controls where text is displayed in that custom format; it does not convert a numeric cell into a text value. The built-in Text category is a separate cell format choice.
Why did Excel remove the leading zeros?
Excel probably interpreted the entry as a number, and numbers do not retain insignificant leading zeroes. Set the cells to Text before entry, use an apostrophe for an individual value, or apply a zero-padded custom number format if the value must stay numeric.
Can I convert numbers already in cells to text?
Yes, but changing the number format alone is not a dependable conversion. You can use a conversion method or a formula that returns text, and should verify the result. If Excel has already discarded leading zeroes or altered a long digit string, recover the original value from the source rather than relying on formatting.
Does Text format stop formulas from working?
A formula entered into a Text-formatted cell may be treated as text and shown literally. Set the cell to General before entering the formula, or change the format and re-enter or re-evaluate the formula. Text formatting does not disable formulas throughout the workbook.
Related
- Excel Shortcut to Select All Data in a Column Quickly
- Excel Shortcut to Select a Whole Column in Seconds
- Excel Shortcut to Insert a Row: Windows and Mac
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel DOS Keyboard Shortcuts: What Actually WorksYes—modern Microsoft Excel has many keyboard shortcuts, including shortcuts for editing cells, navigating worksheets, and formatting data. If…
- Clear Cache in Outlook for Windows 10: Step-by-StepTo clear cache in classic Outlook on Windows 10, first choose which cache is causing the problem: use…
- Disable Autofit in Excel: Keep Rows and Columns FixedTo stop a PivotTable from changing column widths after each refresh, select it, open PivotTable Analyze > Options,…
- Excel Freeze Specific Panes: Rows, Columns, or BothTo freeze specific rows and columns in Excel for Microsoft 365 on Windows, select the cell immediately below…
- How to Make a Fillable Form in Microsoft Word Step by StepTo make a fillable form in Microsoft Word, turn on the Developer tab, add content controls such as…
- Close Excel Shortcut Key for Windows and MacIn Excel for Microsoft 365 on Windows, press Ctrl+W to close the active workbook. To quit the Excel…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.