Data Import & Export

How to Keep Leading Zeros When Importing Text Files into Excel

Maira Mehtab Sep 22, 2026 868 views

Question details

Users need to retain leading zeros on identification numbers when importing delimited text or CSV files into a spreadsheet.

Product: Excel
Device & OS: not provided
Scenario: Importing text files with data separated by delimiters where specific columns contain identification numbers starting with zeros.
Observed behavior: Excel automatically removes leading zeros from the identifiers during import, converting them into standard numeric values which are right-aligned.

Before you start

Ensure you have your original text or CSV file ready and verify the exact number of digits your identifiers should contain (e.g., seven digits) before initiating the import process.

Solution 1Recommended

Import the Data Column as Text

The most effective way to keep leading zeros is to define the specific column's data type as Text during the initial import process.

By default, Excel evaluates incoming data and converts entirely numeric columns into numbers, which strips out any leading zeros. Changing the data type to Text before the import completes prevents this automated conversion.

Launch Data Import

Go to the 'Data' tab on the Excel ribbon and click on 'From Text/CSV' to start the import process.

Select Your File

Locate and select the text or CSV file you wish to import, which will open the import preview window or Power Query Editor.

Change the Data Type

Select the specific identifier column in the preview, locate the data type dropdown, and change its format to 'Text'.

Load the Data

Click 'Load' to bring the data into your spreadsheet with all leading zeros preserved.

Verifying Text Format: Once correctly imported as text, the identifier values will be left-aligned in their cells, confirming they are no longer treated as standard numbers.

Solution 2

Apply Custom Number Formatting for Existing Data

If the data has already been imported and the leading zeros were removed, you can visually restore them using a custom number format.

Manage Data Seamlessly

Import Text Files and Keep Leading Zeros Easily with WPS Office

WPS Spreadsheet provides intuitive data import tools that let you easily define column formats right from the start, ensuring your leading zeros and text identifiers remain perfectly intact without extra effort.

1. Open Data Import: Launch WPS Spreadsheet, navigate to the 'Data' tab, and click on 'Import Data'.
2. Select the File: Browse for your CSV or text file and open it to trigger the Text Import Wizard.
3. Set Column Format: Highlight the column containing your identifiers in the preview window, select 'Text' under the 'Column data format' options, and click 'Finish'.

Fully compatible with Microsoft Excel file formats (.xlsx, .xls, .csv).Intuitive Text Import Wizard to assign 'Text' format to specific columns quickly.Robust custom cell formatting features to restore missing zeros on existing data.Lightweight, fast, and completely free to use.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel automatically remove leading zeros from my data?

By default, Excel treats data consisting entirely of digits as standard numbers. Since leading zeros have no mathematical value in numerical calculations, Excel automatically removes them and right-aligns the data in the cell.

How can I type leading zeros manually without them disappearing?

You can type an apostrophe (') directly before the number (e.g., '012345). The application will treat the cell as text, preserving the leading zero and keeping the apostrophe hidden.

Does applying custom formatting actually change the underlying data?

No, applying a custom number format like '0000000' only changes how the data is visually displayed on the screen. The underlying value stored in the cell remains a standard number without leading zeros.

Can I use a formula to add leading zeros back to my numbers?

Yes, you can use the TEXT function. For example, if your number is in cell A1 and needs to be exactly 7 digits long, use the formula =TEXT(A1, "0000000"). This converts the number into a text string with the necessary leading zeros appended.

Excel & Data Problems