To paste numbers into Excel as text, format the destination cells as Text before pasting plain-text data: select the cells, choose Home > Number Format > Text, then paste. For a value you enter yourself, type an apostrophe first, as in '00123. These methods help preserve leading zeros and prevent Excel from treating codes or long identifiers as numbers.
Why Excel turns pasted numbers into numbers
Excel tries to recognize what you paste and store it in a useful form. A string such as 123 looks like a number, so Excel may store it as a numeric value rather than as text. That is convenient for calculations, but it can change data that only looks numeric: a product code, postal code, account ID, or telephone number is usually an identifier, not a quantity to add or average.
Excel stores numbers and text differently. A numeric value can be used in arithmetic and sorted numerically; text is treated as a sequence of characters. This difference can be hard to spot because both may look the same in a cell. A value of 00123 is a common example: if Excel converts it to the number 123, the original leading zeros are gone. Formatting that cell to display five digits later can make it look like 00123, but does not restore the original text or make the underlying value text.
Long digit strings are another risk. Excel numbers have limited precision—15 significant digits—so a long identifier entered or pasted as a number may lose digits. Formatting it as Text before Excel interprets it is safer than trying to repair it after conversion.
Format the destination cells before pasting
For data copied as plain text from a website, email, or text file, prepare the destination first. In Excel for Microsoft 365 on Windows:
- Select the destination cell or range.
- On the Home tab, open the Number Format list in the Number group and choose Text. You can also open Format Cells with Ctrl+1, select the Number tab, choose Text, and select OK.
- Paste the data into the selected range.
- Check a few values in the formula bar and test an identifier with leading zeros or more than 15 digits.
Preformatting is most dependable when Excel receives plain text. Pasting cells copied from another Excel workbook can also bring their values and formatting, so the source may affect the result. If the pasted entries still behave as numbers, use a conversion method below or import the source as text rather than relying on the destination format alone.
On a Mac, the Format Cells shortcut is Command+1 rather than Ctrl+1. Excel for the web has a Number Format control on the Home tab, although some import and conversion options differ from the Windows desktop app.
Use an apostrophe for individual entries
When typing a single code, prefix it with an apostrophe: enter '00123 and the cell displays 00123. Excel uses the apostrophe as an instruction to treat the entry as text; it does not appear as part of the displayed value. This is a quick option for occasional entries or when you control the data before pasting.
The apostrophe is not a practical fix for a large block unless you add one to each value before Excel interprets the data. It also does not recover digits or zeros already removed by a previous conversion. If another system requires a literal apostrophe in the data, check the exported result: Excel’s entry marker is not normally part of the cell value, but the behavior of other applications depends on how data is copied or exported.
Convert numbers already in the worksheet
If values are already in cells as numbers, changing their number format to Text does not necessarily convert the stored values. For a column that needs conversion, Text to Columns can explicitly set the output type:
- Make a copy of the original column or workbook first, especially if the data is important.
- Select the column, then choose Data > Text to Columns.
- Choose Delimited and select Next. If the values have no delimiter, leave the delimiter boxes clear; the wizard still lets you set the column type.
- At the final step, select the column in the preview, choose Text under Column data format, and select Finish.
Check the result afterward. If a value had already lost leading zeros or precision while stored as a number, converting it to text only converts the current value; it cannot infer the missing original characters. For example, converting numeric 123 to text produces text 123, not text 00123. Restore missing characters only from a reliable source or a documented rule.
Import CSV and other files with identifiers intact
For recurring data from a CSV or text file, importing is often safer than opening the file directly or pasting its contents. In Excel for Microsoft 365 on Windows, use Data > From Text/CSV, select the file, and inspect the preview before loading. In Power Query, set identifier columns to the Text data type before loading the data to the worksheet. This prevents automatic type detection from treating codes as numbers.
CSV files do not store Excel cell formats or data types; they store delimited text. When Excel opens a CSV directly, it may interpret digit-only fields as numbers. Importing lets you control the treatment of columns, but check the preview and the loaded result because separators, decimal conventions, and automatic detection can affect how a file is read. On Mac, available import commands and dialogs can vary by Excel release. If the Windows path is not shown, check the Data tab for the text or CSV import option available in that version.
For a one-time job, a plain-text editor can also be useful: inspect the source there and then paste into cells already set to Text. Do not assume that a file ending in .csv protects identifiers from conversion; the format itself does not preserve Excel-specific text formatting.
Use a formula when you need a text result
A formula can convert an existing numeric value to text. With the number in A2, enter this formula in B2 to produce a text value with no added digit padding: =TEXT(A2,"0"). If A2 contains 123, the formula returns the text 123. The TEXT function applies a number format while returning text, so the format code needs to match the intended display.
For a fixed five-character code, with the number in A2, =TEXT(A2,"00000") returns text 00123 when A2 contains 123. This is appropriate only when five digits are a real rule for the data. It does not know whether a particular code should have a different length, and it cannot recover digits lost from a long number. After filling a formula down, you can copy the results and use Paste Special > Values if you need fixed text rather than formulas; verify the result before removing the original data.
When a source value was already stored as text, a formula may be unnecessary. Avoid converting identifiers to numbers simply to make them easier to manipulate: text values can still be searched, matched, filtered, and sorted, though sorting text may follow character order rather than numeric order.
Text format is not the same as a custom number format
Custom number formats change how a numeric value is displayed without changing its underlying type. For example, a custom format that displays five digits can show numeric 123 as 00123. The cell still contains the number 123, so it remains suitable for arithmetic but is not text. A custom format can be useful when you need numeric values with consistent display, but it is not the right solution when another system requires a text identifier.
Likewise, changing a cell to Text after pasting is not the same as converting its contents. If the value is still numeric, the format change may affect how later entries are interpreted but will not, by itself, reliably transform the existing value. Use Text to Columns, an appropriate formula, or a controlled re-import when the stored type must change.
Common problems and how to check the result
A cell’s appearance alone may not tell you whether Excel stored its contents as text. Alignment is not a reliable test because formatting can change alignment. Instead, inspect the formula bar and test how the value behaves. For a critical import, check several rows, including the longest ID and entries with leading zeros.
- Zeros disappeared: Excel probably interpreted the entry as a number. Re-import from the original source as Text, or restore the zeros only if the required width is known.
- Digits changed at the end of a long ID: the value may have exceeded Excel’s numeric precision. Re-import the original data as text; changing the current value’s format cannot restore lost digits.
- Numbers are left-aligned: this can be a clue that entries are text, but alignment may be manually formatted. Confirm the data type rather than relying on appearance.
- Calculations no longer work as expected: text entries are not ordinary numbers. If the column is truly a quantity, convert it to numbers; if it is an identifier, keep it as text and adjust formulas or comparisons accordingly.
- Only some pasted cells changed: source formatting, mixed data, or different paste methods may have produced mixed types. Select the full column and use a deliberate import or conversion process.
Frequently Asked Questions
How do I paste numbers in Excel without losing leading zeros?
Format the destination as Text before pasting plain-text data, or prefix individually entered values with an apostrophe. For files, import the identifier column as Text. If Excel already removed the zeros, retrieve the original values or apply a known fixed-width rule; the missing zeros cannot be inferred automatically.
Does formatting cells as Text convert existing numbers?
No. Text formatting mainly guides how new entries are interpreted; it does not reliably change existing numeric values into text. Use Data > Text to Columns and set the column format to Text, or create a text result with a suitable formula.
Can I paste a 16-digit account number into Excel?
Yes, but make sure Excel treats it as text before conversion. Excel numbers retain only 15 significant digits, so a long identifier stored as a number may be altered. Import or paste it as text from the original source and verify all digits.
Why does Excel show a green triangle in a number stored as text?
The warning commonly indicates that a value looks like a number but is stored as text. For IDs and codes, that may be intentional and the warning can be ignored. For quantities that need arithmetic, use the warning menu or an appropriate conversion to turn the values into numbers.
Can I keep a number numeric and still show leading zeros?
Yes. Apply a custom number format when you need a numeric value displayed at a fixed width, such as a five-digit code. That changes its appearance, not its underlying type. Use text instead when the identifier must remain an exact character string.
Does a CSV file preserve Excel’s Text format?
No. CSV stores text fields separated by delimiters, not Excel cell formats. Import the CSV and set the relevant column to Text, then check the loaded values before using or sharing the workbook.
Related
- Excel Number Custom Format Codes: Examples and Tips
- Excel Not Letting You Insert an Image? Causes and Fixes
- Excel No Color Fill: How to Remove a Cell’s Background
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel File Format Guide: XLSX, XLS, CSV and Other TypesAn Excel file format is the type of file used to save a workbook, identified by its extension.…
- Double Space in Word: Set 2.0 Line SpacingIn Microsoft Word, double spacing means setting line spacing to 2.0; it does not change the number of…
- Excel Vertical Text: How to Rotate Text in CellsTo make text vertical in Excel for Microsoft 365 on Windows, select the cell or range, then choose…
- Excel Named Range Data Validation List: Setup and FixesYes. In Excel for Microsoft 365 on Windows, set Data Validation to allow a List, then enter the…
- Excel Not Responding? Recover an Unsaved FileIf Excel is not responding and your work has not been saved, do not force-close it right away.…
- Google Sheets IF Zero: How to Handle Zero Values in FormulasTo test whether a cell equals zero in Google Sheets, use an IF formula such as =IF(A2=0,"Zero","Not zero").…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.