Formatting Issues

How to Prevent Excel from Removing Leading Zeros in Phone Numbers

Maira Mehtab Sep 20, 2026 868 views

Question details

The user needs to prevent the software from automatically removing the leading zeros from numeric entries like phone numbers.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Entering new phone numbers manually or importing data containing numbers that start with a zero.
Observed behavior: The application automatically drops the leading zeros because it interprets the input as standard numeric or decimal values (e.g., converting 0123 to 123).

Before you start

Before attempting to fix the issue, identify whether you are typing new numbers manually or importing existing data, as the method to preserve leading zeros will depend on your data entry approach.

Solution 1Recommended

Pre-format Cells as Text Before Data Entry

Changing the cell format to Text before you type forces the application to treat your input exactly as entered, preserving all leading zeros.

By default, spreadsheet cells use a 'General' format that automatically applies mathematical rules to your inputs. Pre-formatting the area as Text disables these rules.

Select the target cells

Highlight the specific cells, rows, or entire column where you plan to enter the phone numbers.

Open the format menu

Right-click the selected area and choose 'Format Cells', or use the dropdown menu in the Number group on the Home tab.

Apply Text formatting

Select 'Text' from the list of categories on the left side of the dialog box, then click 'OK'.

Enter the data

Type your phone numbers with the leading zeros. The spreadsheet will now keep the zeros intact and display them exactly as typed.

Existing Data Cannot Be Restored: Changing the format to Text after the zeros have already been removed will not restore them. You must re-enter the phone numbers manually.

Solution 2

Use an Apostrophe for Quick Manual Entries

If you only need to enter a few phone numbers, you can use a shortcut to force the software to treat individual entries as text.

Solution 3

Use Power Query for Imported Data

When importing CSV files or external datasets, use Power Query to explicitly define the column data types during the import process to prevent zeros from dropping.

Efficient Spreadsheet Management

Use WPS Spreadsheet to Manage Data with Leading Zeros

WPS Office provides a highly capable, user-friendly Spreadsheet tool that handles text formatting and data imports flawlessly. You can easily preserve leading zeros in phone numbers, product codes, and ZIP codes using its intuitive interface.

1. Open WPS Spreadsheet: Launch WPS Office and create a new blank spreadsheet or open your existing workbook.
2. Select the Data Range: Highlight the cells or the entire column where you will enter or paste your phone numbers.
3. Format as Text: Right-click the selection, choose 'Format Cells', navigate to the 'Number' tab, select 'Text', and click 'OK'.
4. Input Your Data: Type or paste your numbers into the formatted cells. WPS Spreadsheet will automatically retain all leading zeros.

Easily format entire columns as text to preserve leading zeros in seconds.Excellent compatibility with Microsoft Excel (.xlsx) formats and CSV files.Dedicated text import wizard prevents automatic data formatting errors.Lightweight, fast, and completely free to download and use.

Frequently Asked Questions

Why does Excel remove the zero at the beginning of my numbers?

By default, spreadsheet software uses a 'General' number format. It interprets numbers starting with a zero as mathematical values. Because a leading zero does not change the mathematical value of a whole number, the software automatically drops it to simplify the display.

Can I restore leading zeros that have already been removed?

Simply changing the cell format to Text after the fact will not restore lost zeros. You will need to either re-enter the data manually, or use a Custom number format (such as '0000000000' for a 10-digit number) to visually force the missing zeros to appear.

How do I keep leading zeros when opening a CSV file?

CSV files store raw text. If you open a CSV directly by double-clicking it, the software parses the data and drops the leading zeros again. To prevent this, always import the CSV using the 'Data > From Text/CSV' menu and specify the column format as Text before loading the data.

Document & File Issues