In Excel, Text format tells a cell to treat what you enter as text rather than interpreting it as a number, date, or formula. To apply it in Microsoft Excel for Microsoft 365 on Windows, select the cells, then choose Home > Number Format dropdown > Text; apply the format before entering or pasting the values you need to preserve.
What Text format means in Excel
Text is a cell number format, not a separate kind of Excel workbook. It is useful when the contents may look like numbers but are actually labels or identifiers: product codes, postal codes, account references, phone numbers, or values that must retain leading zeros. For example, the code 00427 should remain 00427, not become 427.
Excel normally interprets entries according to their appearance and context. It can remove leading zeros from numeric-looking entries, display a long number in scientific notation, or recognize an entry as a date. Text format helps prevent that interpretation for values entered after the format is applied. The cell can still contain letters, punctuation, and spaces as well as digits.
This does not change the workbook’s file extension. A workbook saved as .xlsx can contain text-formatted cells, numeric cells, formulas, charts, and other content. Conversely, a text file such as .csv can contain rows of data without preserving Excel’s cell formatting.
When to format cells as Text
Use Text format when the value identifies something rather than measuring it. A postal code is not a quantity to add, and an employee ID is not a number whose value is useful in arithmetic. Keeping such entries as text preserves their intended characters and avoids accidental changes during entry or import.
- Leading zeros: Codes such as 00731 or 000045 should retain every zero.
- Long identifiers: Tracking numbers and reference codes may contain more digits than Excel can preserve accurately as numbers.
- Phone numbers: A phone number is a label, often including a country code, spaces, or punctuation, rather than a value for calculation.
- Mixed codes: Entries such as AB-0042 or 12E03 should remain exactly as supplied.
- Values that resemble dates: A code such as 3-4 may be interpreted as a date unless handled as text.
Text is usually the wrong choice for values you intend to calculate, such as prices, quantities, dates used in date arithmetic, or percentages. Those should normally be stored using suitable numeric or date values and displayed with an appropriate number format.
How to apply Text format
For new entries in Excel for Microsoft 365 on Windows, select the target cells or column and use Home > Number Format dropdown > Text. You can also open the Format Cells dialog, select the Number tab, choose Text in the Category list, and select OK. Then type or paste the values.
- Select the cells or the whole column where the text values will go.
- Choose Text from the Number Format dropdown on the Home tab, or set Category to Text in Format Cells.
- Enter or paste the values, checking a few examples to make sure zeros and punctuation remain.
If you only need to force one entry to be text, type an apostrophe before it, such as ‘00123. Excel uses the apostrophe as an entry marker and does not display it as part of the cell value. This is handy for a small number of entries, but formatting a column first is clearer when many values are involved.
On Excel for Mac, the Home tab also has a number-format control, and Format Cells provides the Text category. The wording or layout can differ slightly by release. In Excel for the web, select the cells and use the number-format control on the Home tab; if the workbook or import workflow offers different options, use the desktop app for more control over data import.
Text format will not repair existing data
Changing a cell that already contains a number to Text does not reliably convert that stored number into text or restore information Excel has discarded. For instance, if 00427 was entered as a number and Excel stored it as 427, changing the format afterward cannot know how many zeros were originally present. The original source data is needed to recover them.
For existing values that are still intact but need to be stored as text, one approach is to create a helper column using a text-conversion formula, then copy the results and use Paste Special > Values. Check the output carefully before replacing the source. If the value has already lost leading zeros or digits, a formula cannot reconstruct them without a rule or a reliable original record.
A related limitation matters for long numeric-looking entries: Excel retains only 15 significant digits when it stores a value as a number. If an identifier is longer, format the destination as Text before entry or import. If Excel has already rounded the digits, changing the format later will not bring the original digits back.
Importing text data without unwanted changes
Importing a CSV or other delimited file by opening it directly can lead Excel to infer data types. It may treat a code as a number, strip leading zeros, or interpret a field as a date. For a more controlled import in Excel for Microsoft 365 on Windows, choose Data > From Text/CSV, select the file, and use Transform Data when you need to set column types before loading.
In the Power Query editor, select the relevant column and set its data type to Text using the data type control. Confirm that the preview preserves the values as required, then choose Close & Load. This is especially useful for repeatable imports because the query can retain the type choice when refreshed, though it is still sensible to inspect the results when the source file changes.
For a one-time import, inspect the import preview and available transformation options rather than assuming Excel will recognize every identifier correctly. If you have a workbook containing the source data, a safer handoff can be to store identifiers in a text-formatted column before sharing it. A CSV file itself does not carry Excel cell formats or type settings, so recipients may need to import it with the correct column type.
Text cells, calculations, and sorting
Text values are not interchangeable with numbers. A cell containing the text 25 may look like a numeric value, but it can behave differently in calculations, comparisons, charts, and sorting. If you need to sum quantities, keep them numeric. If you need to preserve a code that happens to contain digits, keep it as text and do not rely on it for arithmetic.
Text sorting is generally alphabetical or lexicographic, not numerical. For example, text entries 1, 20, and 3 can sort in that order because Excel compares characters rather than treating each entry as a quantity. This is expected for identifiers. If you need numerical order, use a separate numeric value or a carefully controlled sort key while retaining the original code.
Text-formatted cells can also affect formula entry. If a cell is formatted as Text before you type something beginning with an equals sign, Excel may display the formula characters instead of calculating the formula. To correct that, change the cell format to General, then re-enter the formula. Do not change a column of identifiers to General simply to fix one formula if preserving the identifiers is important.
Excel may show a green triangle or an error-checking warning for a number stored as text. That warning is not automatically a problem. For an ID or postal code, the text storage may be intentional. Use the warning’s conversion option only when the value is truly a number and calculations or numeric sorting are required.
Text cell format versus TXT and CSV files
The phrase “Excel text format” can also mean a text-based file saved from Excel. These are different ideas: Text cell format controls how individual cells are interpreted, while TXT and CSV describe file formats used to store data.
- Text cell format: A setting inside a workbook, such as .xlsx, for values in selected cells.
- CSV: Comma-separated values, a plain-text table format commonly used to exchange data. It generally does not preserve workbook formatting, formulas as formulas, multiple sheets, or Excel-specific features.
- TXT: A plain-text file. Excel’s tab-delimited text option separates columns with tab characters rather than commas.
To create one of these files in Excel for Windows, use File > Save As, choose a location, and select the required file type in the Save as type list. Excel may warn that some workbook features cannot be saved in a text format. A CSV or tab-delimited text file generally represents only the active worksheet, so check which sheet is selected and keep the original .xlsx workbook if you need formatting, formulas, or multiple sheets.
CSV delimiter behavior can depend on regional settings and the application that reads the file. A recipient may expect commas, semicolons, or another delimiter, and character encoding can affect accented or non-Latin text. If compatibility matters, agree on the delimiter and encoding with the receiving system and verify a sample after export. A CSV file is not a dependable way to preserve leading-zero display rules; import the relevant fields as text on the receiving side.
Common mistakes to avoid
- Formatting after entry: Set Text before entering or importing values. A later format change cannot recover leading zeros or digits already lost.
- Using Text for everything: Keep values numeric when you need arithmetic, numeric sorting, or date calculations.
- Assuming a CSV remembers formats: CSV stores plain data, not Excel’s cell-format settings. Re-import with the correct types.
- Ignoring formula behavior: A formula typed into a Text-formatted cell may be treated as literal text; change the format and enter it again if calculation is intended.
- Converting identifiers because of a warning: Check what the data represents before choosing Convert to Number.
A practical rule is to decide what each column means before importing or typing it. Mark identifiers as text; keep measurable quantities and dates in appropriate data types. That small decision prevents many downstream problems with matching, sorting, and sharing files.
Frequently Asked Questions
How do I change a cell to Text in Excel?
Select the cell or range, then choose Home > Number Format dropdown > Text. Apply the setting before entering or pasting values if you need to preserve leading zeros or prevent date conversion.
How do I keep leading zeros in Excel?
Format the destination cells as Text before typing or importing the values. For an individual entry, start it with an apostrophe, such as ‘00123. If zeros were already removed, retrieve the original value from the source.
Does changing a number to Text bring back missing digits?
No. Changing the format does not restore leading zeros or digits that Excel has already discarded or rounded. Re-import the original data as text or recover it from a reliable source.
Why does Excel show a green triangle for a text number?
Excel’s error checking may flag a numeric-looking value stored as text. It may be intentional for an ID or code. Convert it to a number only if you need numeric calculations or numeric sorting.
Is CSV the same as Text format in Excel?
No. Text is a cell format inside a workbook. CSV is a plain-text file format for exchanging tabular data and does not preserve Excel’s cell formatting or workbook features.
Related
- Excel Accounting Solver: What It Does and How to Use It
- Excel Budget Spreadsheet Free? Find Templates and Tips
- Replace Excel Formatting: Find and Change Cell Styles
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Set Up Automatic Email Forwarding in OutlookTo automatically forward every new message in Outlook, open Settings, go to Mail > Forwarding, turn on forwarding,…
- Out of Office in Outlook on the Web: A Setup GuideTo set up an out-of-office reply in Outlook on the web, open Settings, go to Automatic replies, turn…
- Add a Header to One Page in Word: A Step-by-Step GuideYes. To add a header to just one page in Word, isolate that page in its own section…
- Excel Very Slow Loading Data? Causes and FixesIf Excel is very slow loading data, first identify whether the delay happens while opening the workbook, recalculating…
- How to Freeze Selected Rows in Excel While You ScrollTo freeze selected rows in Excel, click the cell in column A immediately below the last row you…
- How to Embed a Video in PowerPoint and Set It to PlayTo embed a video in PowerPoint on Windows, open the slide, choose Insert > Video > This Device,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.