How to use
- Drop a
.csvor.xlsxfile, or paste cells copied from Excel. A CSV is read with every value as text, so the 0 in00123is still there at this stage. - 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. - If needed, set pad to length (for example 5) and the columns to convert to numbers (
1,234,₩500). - 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 code06236becomes6236and employee ID000451becomes451. - 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
0and 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:
1234becomes01234and12becomes00012(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,234becomes 1234,₩500becomes 500,12becomes 12 (3 cells).
With the CSV ="00123" option on, the ZIP cells are saved as ="00123", ="06236" and ="01234".
Related tools
- Stack several steps: Excel & CSV Workbench
- Convert between CSV and xlsx, and encodings: CSV to Excel Converter
- Bulk-replace text or stray zeros: Find & Replace in Excel
- Check each column’s type: Column Statistics
Your file is processed only in your browser and is never sent to a server.