1. Home
  2. Spreadsheets & Data
  3. Keep Leading Zeros

Keep Leading Zeros

Stop Excel from dropping leading zeros in ZIP codes and IDs from CSV files.

For every format and option in one place, use Excel & CSV Workbench →

Runs in your browser · No upload
Leave the columns empty to auto-detect columns with zero-led or 12+ digit numbers. xlsx saves them as text cells; for CSV there is an ="00123" option.

Operations

Keep leading zeros and long numbers as text, or turn text like 1,234 back into numbers.
Columns to keep as text
Leave empty to auto-detect columns with numbers starting with 0 or 12+ digit numbers
Columns to convert to numbers instead (1,234 · ₩500 · 12원)
Files are processed in your browser and never sent to a server.

How to use

  1. Drop a .csv or .xlsx file, or paste cells copied from Excel. A CSV is read with every value as text, so the 0 in 00123 is still there at this stage.
  2. Pick the columns to keep as text. Leave it empty and the tool finds columns that have a number starting with 0 (00123) or any number with 12 or more digits.
  3. If needed, set pad to length (for example 5) and the columns to convert to numbers (1,234, ₩500).
  4. Choose a format under Export. xlsx stores the picked columns as text cells, so the zeros survive when opened in Excel. For CSV, turn on the ="00123" option (explained below).

Why Excel drops the zeros

  • When Excel opens a CSV it turns anything that looks like a number, such as 00123, into a number and drops the leading zeros. That is how ZIP code 06236 becomes 6236 and employee ID 000451 becomes 451.
  • Excel keeps only 15 significant digits in a number. A 13-digit order number is shown as 1.23457E+12, and a 16-digit card number or long ID has everything after the 15th digit replaced by zeros, which cannot be undone.
  • Values already truncated in a file saved from Excel cannot be recovered. Convert the original CSV with this tool first.

Two ways to save

Way Opened in Excel In other programs
xlsx (picked columns as text cells) 00123 as is read as is
csv with the ="00123" option 00123 as is (the cell holds the formula ="00123") the value reads as the literal text ="00123"
csv, option off 123, the zeros vanish 00123 as is

If the CSV is going into a database or another program, leave the option off. If it is meant to be opened in Excel, turn it on. Exchanging xlsx files is safer when possible. The option applies only to values made purely of digits in the columns you kept as text.

Auto-detection rules

  • A column with at least one value that is text starting with 0 and made only of digits (00123, 01234).
  • A column with at least one number of 12 or more digits, whether it is text or a numeric cell. That is the length at which Excel starts showing exponents.
  • Columns you picked for number conversion are excluded. The detected column names appear in the step report.

Padding to a length

In the picked columns, a value made only of digits and shorter than the length gets zeros added in front. A ZIP code stored as the numeric cell 1234 becomes 01234, while a value with letters such as AB1 is left alone. Padding applies to every picked column with the same length, so add another step for columns that need a different length.

Text to numbers

Columns picked under “convert to numbers” are handled the opposite way. Surrounding spaces, currency marks (₩ ₩ $ \), 원 and thousands commas are removed, and the value is read as a number.

Input Result
1,234 1234
₩500 500
12 12
12원 12
abc unchanged (the report says 1 cell could not become a number)

Commas that are not in groups of three, as in 1,2,3, are not read as a number, and numbers longer than 15 digits are left as text because they would lose precision. Converting 007 to a number drops its zeros, so use this on amount columns where you need sums and sorting.

Example

Columns 우편번호 (ZIP), 계좌 (account) and 금액 (amount) hold 00123 / 1234567890123 / 1,234, 06236 / 9876543210987 / ₩500 and 1234 / 12 / ␠12␠. Leave the columns empty, convert 금액 to numbers and pad to 5:

  • Auto-detect: 우편번호 and 계좌 are kept as text.
  • Padding: 1234 becomes 01234 and 12 becomes 00012 (2 cells). The account column was detected too, so it is padded as well. To leave it out, pick only the ZIP column yourself.
  • Amount: 1,234 becomes 1234, ₩500 becomes 500, 12 becomes 12 (3 cells).

With the CSV ="00123" option on, the ZIP cells are saved as ="00123", ="06236" and ="01234".

Your file is processed only in your browser and is never sent to a server.

Can't find the tool you need?

Tell us what you want to do. We build the most requested tools first.