Excel Text to Columns: How to Keep Leading Zeros Safely

Yes—Excel Text to Columns can keep leading zeros, but you must set the affected column’s format to Text in the wizard before you finish. If you leave it as General, Excel may interpret an entry such as 00123 as the number 123 and remove the zeros.

Set the column to Text in Text to Columns

In Excel for Microsoft 365 on Windows, select the cells you want to split or convert, then choose Data > Text to Columns. In the wizard, choose Delimited or Fixed width, set the split options you need, and continue to Step 3. The important setting is at this last step: click the relevant column in the data preview, choose Text under Column data format, and then click Finish.

Select the source column or range. If the results should go somewhere else, set a blank destination in the wizard rather than overwriting nearby data.
Choose Data > Text to Columns, then choose Delimited or Fixed width. Use Delimited for separators such as commas or tabs; use Fixed width when the split positions are based on character positions.
Set the delimiter or break positions and click Next until you reach Step 3.
In the preview, select the column that contains the codes or numbers with zeros. Choose Text under Column data format. Repeat for any other columns that should remain text.
Check the preview, set the destination if needed, and click Finish.

For example, if the source contains 00123, the Text format tells Excel to retain those five characters as text. A column left as General may instead become the numeric value 123. If you are not splitting a single column, choose Delimited and clear the delimiter checkboxes so Excel does not divide the values unexpectedly; then proceed to Step 3 and choose Text.

Why Excel removes leading zeros

Excel normally treats entries made up of digits as numbers when it can. In arithmetic, 00123 and 123 have the same numeric value, so Excel stores 123 and displays it without the extra zeros. That behavior is often useful for amounts and counts, but it is wrong for identifiers whose characters matter.

Postal codes, product codes, account references, employee IDs, and some telephone numbers are examples of values that look numeric but are not quantities. You would not add two postal codes or calculate an average employee ID. They are labels, so preserving their exact text is usually the right choice. Setting the Text to Columns output to Text tells Excel to treat those entries as labels rather than numbers.

Text to Columns does not automatically preserve every source appearance. It interprets each output column according to the format selected in Step 3. General is the default in many situations and can convert digit strings to numbers, and values that resemble dates may be interpreted as dates. Choose Text for identifiers and any other values that must remain exactly as written.

Choose Text or General for each output column

Do not necessarily set every column to Text. Choose the format based on what the data means, not just how it looks. A file might contain an ID that needs its zeros, a date that should become a usable Excel date, and a quantity that should be numeric. Text to Columns lets you select columns individually in the Step 3 preview, so you can apply different formats to different parts of the same split.

Choose Text for identifiers, codes, postal codes, phone numbers, and digit strings whose exact characters must be retained.
Choose General when Excel should infer a suitable type, and numeric calculations or date operations are expected. Check the result carefully if the source contains ambiguous values.
Choose Date for a column of dates when you know the source order, such as month-day-year or day-month-year. Selecting the wrong order can produce the wrong date.

For instance, splitting a record like 00123,2026-04-18,7 into three columns may call for Text for the first field, Date for the second, and General for the final quantity. The exact delimiter and date interpretation depend on the source. Confirm each preview column rather than assuming that one setting is right for the whole row.

What Text format preserves—and what it cannot restore

Choosing Text preserves the characters still present in the source. It cannot infer zeros that Excel removed earlier. If a CSV was opened by double-clicking and Excel already changed 00123 to 123, the original width is no longer present in that cell. Applying Text to the existing number will turn 123 into the text 123; it will not recreate 00123 automatically.

If you know that every code has a fixed width, you can rebuild a text version from the numeric value. With the shortened number in A2 and a required five-character code, enter =TEXT(A2,"00000") in another cell. For a value of 123, the result is the text 00123. Change the number of zeros in the format pattern to match the required width. This works only when the correct width is known; it cannot distinguish an original 00123 from 000123 if both were reduced to 123.

Keep the original data or a copy before converting a column. Text to Columns can write results over the selected range, and recovering exact original strings may be impossible once information has been discarded. For an authoritative version of an ID, return to the source system or original file if the width or characters are uncertain.

Import a CSV without losing zeros

If the zeros disappear when you open a CSV, the problem may occur before you use Text to Columns. Opening a CSV directly can prompt Excel to interpret its contents, and digit-only fields may be converted to numbers. A safer approach in Microsoft 365 for Windows is to import the file through Data > From Text/CSV, choose the file, and select Transform Data to open Power Query.

In Power Query, select the identifier column and change its data type to Text before loading the data. You can use the data-type control in the column header or the Transform > Data Type ribbon command. Confirm that the preview shows the leading zeros, then choose Home > Close & Load. If automatic type detection has already assigned a numeric type, change that type to Text and inspect the preview; if the zeros are already absent there, import again from the original file and set the type before conversion.

Power Query is useful for repeatable imports because its transformation steps can be refreshed when the source file changes. Text to Columns is often quicker for a one-time split on a worksheet. In either workflow, check the source and the result: if the source file itself contains quoted or unquoted values such as 00123, the import preview should retain those characters when the column is treated as text.

Custom number formats are not the same as text

A custom number format can make a numeric value look as if it has leading zeros without storing those zeros as characters. For example, formatting the number 123 to display five digits can show 00123 while the underlying value remains 123. This is useful when the value is genuinely numeric and every entry should display at a fixed width.

That display is different from storing the code as text. A formula referring to the formatted cell still uses the number 123, and exporting or copying the value may not preserve the displayed zeros in the way an identifier requires. Text is usually safer when exact characters matter outside the worksheet. A custom format is appropriate when you need numeric behavior and only want a consistent visual display.

Text to Columns may also replace existing formatting when it writes converted results. If a column relies on a custom number format, inspect it after conversion. Decide whether the goal is a numeric value displayed with padding or a text value that actually contains the zeros; those are different outcomes and should not be treated as interchangeable.

Common problems and checks

The zeros still vanish: Reopen Text to Columns and verify that you selected the correct preview column and chose Text in Step 3. Selecting a different column or leaving the format on General will not protect the intended field.
The values split at the wrong places: Review the delimiter settings in Step 2. A comma, tab, space, or other selected delimiter can divide text into extra columns. For a single unsplit field, clear delimiters or use suitable fixed-width settings.
Dates or decimals change unexpectedly: Check the Step 3 format for each column. General can interpret values, while a date column may need an explicitly selected date order. Text avoids such interpretation when the characters themselves must be retained.
Existing worksheet content is overwritten: Set a destination in the wizard or copy the source to a safe area first. Make sure the destination range has room for every output column.
The source had zeros, but the worksheet does not: Text format cannot recover missing characters. Reimport from the untouched source or restore the values using a known fixed width.

After finishing, click a few output cells and inspect the formula bar. A text value such as 00123 should still show all five characters. You can also check whether Excel treats a cell as text by looking for a green error indicator in some configurations, but its presence or absence is not a reliable test by itself; inspect the actual value and use the column’s format or a formula check if needed.

Frequently Asked Questions

Does Text to Columns keep leading zeros automatically?
No. It can keep them when you select the relevant column and set its format to Text in Step 3. Leaving the column as General can cause Excel to remove the zeros when it converts the entry to a number.

Can I use Text to Columns on a column that is not being split?
Yes. Choose Delimited, clear the delimiter options so the values are not divided, continue to Step 3, select the column in the preview, and choose Text. Check the preview and destination before clicking Finish.

Can I restore zeros after Excel has removed them?
Only if you know how many zeros belong in each value or can retrieve the original data. A fixed-width code can be padded with a formula such as =TEXT(A2,"00000") when A2 contains the shortened number and the intended width is five characters. Without that information, Excel cannot know the original string.

Should product codes and postal codes be stored as text?
Usually, yes, when they are labels rather than values for arithmetic. Text preserves leading zeros and avoids treating a code as a quantity. Use a numeric format only when numeric calculations are genuinely required and a display format is sufficient.

Is importing a CSV different from using Text to Columns?
Yes. Text to Columns works on data already in a worksheet; importing through Data > From Text/CSV lets you set a column’s type in Power Query before loading it. For repeated files or values that are at risk of being converted on opening, importing and setting the field to Text is often the safer route.

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.