CONTENTS

Excel Leading Zeros Disappear — Complete Fix for Phone Numbers, ZIP Codes, and Codes

Fix the Excel leading zeros disappear issue in 1 minute, and learn how to preserve zeros when pasting or importing CSV files. Choose the method that fits your goal: restore the display only or preserve the actual value as text.

Quick Fix (1 Minute)

Before pasting, change the column format to Text (Ctrl+1).
For values that have already changed, use a Custom format such as 00000 to restore the display only.
For CSV or web data, use Data > From Text/CSV, set the column Data Type = Text, and then load it.

Why Do Zeros Disappear?

Excel interprets the input as a number and removes leading zeros. Manage codes (phone numbers, ZIP codes, and product codes) as text, or store them as numbers and use a display format to show the zeros.

Restore Values That Have Already Changed

Restore the Display Only

Ctrl+1 → Custom → 00000, 000-0000-0000, and so on.

Convert the Actual Value to Text

=TEXT(A2,"00000")
=RIGHT(REPT("0",5)&A2,5)
=TEXT(A2,"000-0000-0000")

Preserve Zeros From the Start

Before pasting, set the column format to Text.
In Data > From Text/CSV: set the column data type to Text and use locale.
In Power Query, set the data type to Text.

Fix Lookup and Sorting Mismatches

// Number → text
=TEXT(A2,"0")
// Text number → number
=VALUE(A2)
// Remove spaces and NBSP, then convert to a number
=VALUE(TRIM(SUBSTITUTE(A2,CHAR(160),"")))

Practical Examples

Original	Expected Value	Method
12	00012	TEXT(A2,”00000″)
10	10-0000-0000	TEXT(A2,”00-0000-0000″)

Checklist

Use text for codes and numbers for calculated values.
For CSV files, use From Text/CSV and set the column data type to Text.
Use Power Query to lock data types and automate recurring tasks.
Before lookups, standardize data types with TEXT/VALUE.

Troubleshooting

Issue	Cause	Solution
010→10	Recognized as a number	Set the format to Text or add an apostrophe (‘) prefix.
Zeros removed from CSV	Automatic formatting	Use From Text/CSV and set the column data type to Text.
Lookup mismatch	Mixed numbers and text	Standardize with TEXT/VALUE.

피벗테이블이 ‘개수’로 나올 때 — Quick Fix(90초)

Excel Leading Zeros Disappear: Fix Phone Numbers and ZIP Codes