Excel Text to Columns: Why Leading Zeros Disappear

How-to basics · Updated November 21, 2026

Yes. Excel’s Text to Columns can remove leading zeros when a column is set to General, because Excel interprets digit-only entries such as 00123 as the number 123. To keep the zeros, choose Text for that column in the wizard before finishing, or use an import method that assigns the column a text data type.

Why leading zeros disappear

Leading zeros are meaningful in identifiers, but they do not change the value of an ordinary number: 00123 and 123 represent the same numeric quantity. Excel therefore often treats a string made up of digits as a number when it converts or imports data. Numeric cells store the value, not the original number of leading zeros, so the zeros are not retained as part of that value.

This is common with product codes, postal codes, employee numbers, account references, and other codes that look numeric but are not used for arithmetic. For example, a source field containing 00481 may appear as 481 after conversion. Text to Columns is not randomly deleting characters; its General conversion is changing how Excel stores the field.

The same conversion can affect more than leading zeros. General may recognize date-like entries as dates, and long digit strings may be treated as numbers. Excel retains only 15 significant digits of numeric precision, so a long identifier converted to a number can lose digits as well. Identifiers should generally be kept as text when their exact spelling and length matter.

What Text to Columns does

Text to Columns separates the contents of one column into multiple columns. You can split at delimiters such as commas, tabs, or spaces, or set boundaries at particular character positions. The conversion wizard also lets you choose how each resulting column is interpreted. That choice is crucial: selecting Text tells Excel to preserve entries as text rather than converting digit-only strings into numbers.

For example, suppose A2 contains the text 00123, and you split a column of codes at a hyphen. If the resulting code field is left as General, Excel can convert 00123 to 123. If that output field is set to Text before you finish, it remains 00123. The delimiter determines where Excel splits the data; the column data format determines how it stores each piece.

Text to Columns can also be used on a single column even when you are not trying to split it. In that case, it may still convert values according to the selected format. If you only want to preserve the existing contents, check the preview and the selected column format before clicking Finish.

How to preserve zeros in the wizard

In Excel for Microsoft 365 on Windows, use these steps for a column that should remain text:

  1. Make a copy of the source data or workbook, especially if the conversion will overwrite existing cells.
  2. Select the column you want to split, then choose Data > Text to Columns.
  3. Choose Delimited if characters such as commas or hyphens mark the split points, or Fixed width if fields line up at consistent character positions. Select Next as needed to reach the column-format step.
  4. In the data preview, click the column containing the codes with leading zeros. Under Column data format, choose Text. If different output fields need different treatment, select and format each relevant preview column separately.
  5. Optionally set a Destination in an empty area so the results do not replace the source. Confirm the preview, then select Finish.

Afterward, check a few results in the formula bar and, if useful, test whether Excel treats them as text. A text value is typically left-aligned by default, though alignment can be changed, so alignment alone is not conclusive. The important check is that the full code, including its zeros, remains in the cell.

The Text to Columns command is also available from Data > Text to Columns in Excel for Mac, although the dialog’s appearance can differ by release. Look for the same column data format setting and choose Text for identifier fields. If the interface differs, consult the preview before finishing rather than assuming the default will preserve zeros.

If the zeros have already disappeared

First determine whether the zeros are truly gone or merely hidden by a number format. Click the cell and look at the formula bar. If it shows 123 while the worksheet displays 00123, a custom format may be adding zeros for display. If both show 123, the original width is no longer recorded in the numeric value.

If the original data still exists in an unmodified file, re-import or repeat the conversion and set the field to Text. This is the safest fix because it preserves the source exactly. If you only have the converted number, you can reconstruct zeros only when you know the intended length or another rule. For instance, if every code must be five characters, the number 123 can be formatted as 00123. Without a known length or source record, Excel cannot infer whether the original was 0123, 00123, or simply 123.

A custom number format can display a fixed width without changing a number into text. For a five-digit display, select the cells, press Ctrl+1 on Windows, choose Number > Custom, and enter 00000 as the format. On Mac, use Command+1 to open Format Cells. This changes how numeric values look, but the underlying value remains numeric. It may be unsuitable when the code must be exported, compared as text, or used by another system that does not receive the display format.

Choosing the right method for your data

The best approach depends on whether the zeros are already present, whether the field has a known fixed length, and what you will do with the result:

  • Keep an exact code from a text source: choose Text in Text to Columns or set the field to Text during import. This preserves the original characters.
  • Display numeric values at a fixed width: use a custom number format such as 00000 when the value is genuinely numeric and display-only padding is sufficient.
  • Create a text version at a known width: use a formula, then copy and paste values if you need a static result. With the numeric value 123 in A2 and a required width of five digits, enter =TEXT(A2,"00000") in another cell; the result is the text 00123.
  • Bring in a file with several fields: use the text or CSV import preview and assign Text to identifier columns before loading, rather than relying on automatic type detection.

Do not choose a custom number format simply because it makes the sheet look right if another application needs the literal zeros. A cell displaying 00123 through formatting may still contain the number 123. Conversely, text values are not automatically suitable for arithmetic, though they can be compared, searched, and exported as codes. Choose storage based on what the field means, not only on its appearance.

Importing files and avoiding repeat problems

Opening a CSV by double-clicking can lead Excel to infer data types, and digit-only fields may be converted to numbers. When the file contains identifiers, use Data > From Text/CSV in Excel for Microsoft 365 on Windows, review the import preview, and set identifier columns to Text before loading if the interface offers type selection. The exact controls can vary with the import experience and file, so inspect the preview and resulting values rather than assuming detection preserved the source.

A CSV file itself does not carry Excel’s cell formatting. Saving a worksheet with a custom display format as CSV will not reliably preserve that format as a rule; CSV stores delimited text values, not workbook formatting. If a receiving system needs codes with leading zeros, verify the exported file in a plain-text editor and confirm that the literal zeros are present. Keep a copy of the original import when the data is important.

Another common trap is formatting a blank destination column as Text after a number has already been converted. Changing the format afterward does not reconstruct the lost characters; it only affects how future entries are interpreted or displayed. Set the data type before conversion, or return to the unconverted source.

Common misconceptions and checks

  • “Text to Columns always strips zeros.” It does not. The risk comes from converting a numeric-looking field as General. Choosing Text for the field preserves its characters.
  • “A cell that looks like 00123 must contain those zeros.” Not necessarily. A custom number format may display leading zeros while the stored value is 123. Check the formula bar and the actual exported data.
  • “Changing the column to Text afterward restores the original.” It cannot recover characters that are no longer stored. Reprocess the original text or apply a known padding rule.
  • “Every digit string is a number.” Codes are often labels, not quantities. If you would not add, average, or otherwise calculate with the field, treating it as text is often the safer choice.

Before finishing a large conversion, test representative rows: a value with leading zeros, one without them, a blank, a longer code, and any field containing letters or punctuation. A small preview can reveal whether delimiters, dates, or numeric conversion will change data unexpectedly.

Frequently Asked Questions

Does Text to Columns remove leading zeros by default?
It can. If a digit-only output column uses General, Excel may convert the value to a number and drop its leading zeros. Choose Text for that column in the wizard to preserve them.

How do I keep leading zeros when splitting a column?
Open Data > Text to Columns, choose the split method, then select the relevant field in the preview and set Column data format to Text before selecting Finish.

Can I get zeros back after Excel removes them?
Only if you have the original source or know a rule, such as a required five-character code length. A number like 123 does not reveal whether its original form was 123, 0123, or 00123.

Will formatting a column as Text restore zeros already removed?
No. It does not recover characters that were discarded during conversion. Re-import the original data as Text or rebuild the code using a known width.

Is a custom number format the same as storing a code as text?
No. A format such as 00000 can display the number 123 as 00123, but the stored value remains numeric. Use text when another system needs the literal code characters.

Why do zeros disappear when I open a CSV in Excel?
Excel may infer that digit-only fields are numbers. Import the file through Data > From Text/CSV and preserve identifier columns as Text, then check the loaded data and exported file.

Related

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.