In Excel: format empty destination cells as Text before typing or pasting an identifier such as 00123. For a CSV, import the identifier column as Text before loading it. A custom format such as 00000 only pads the numeric display; it does not preserve the original stored characters.
On this page7
Before typing or pasting identifiers, select empty destination cells and choose the Text number format. Enter 00123 and check that its leading zeros remain.
For one typed entry, prefix the identifier with an apostrophe, such as '00123, to enter it as text.
If you only need numeric display padding inside the workbook, use a custom format such as 00000. Numeric 123 then displays as 00123; its stored value is still 123.
If the original width is independently known to be five characters, put =TEXT(A2,"00000") in a separate helper cell. Check that constructed text against the source; do not infer the original width from numeric 123 or 7.
For the Windows desktop From Text/CSV workflow: choose Data > From Text/CSV, then Transform Data (Edit in some versions). Select the identifier column and set Data Type to Text. Choose Replace Current if prompted so Text replaces that type-conversion step.
Check the source identifiers and query preview before Close & Load. If an earlier step has already discarded zeros, revisit that step or reimport the original source before numeric conversion. Changing an already shortened number to text does not restore its missing characters.
What this does
When Excel converts 00123 to a number, its stored value is 123. Keep identifiers as text before that conversion. A custom number format such as 00000 changes the display while the value stays numeric; =TEXT(A2,"00000") instead creates a text result for a known five-character width. Neither method establishes how many zeros an unknown original identifier had. CSV export can save the displayed padded characters, but CSV does not retain Excel number-format metadata or a column type. Reopening those characters with automatic numeric conversion can remove the zeros again. Check the exported file as plain text and use an explicitly typed import when bringing it back into Excel. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. For “stop excel from removing a leading zero”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected cells change, and only then apply the same setup to the full sheet. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That is what makes a workspace setting that makes a big sheet comfortable to navigate useful in real work: repeatable, auditable, and not dependent on memory or luck.
Worked example to check
Worked example: identifier 00123 must stay five characters long. Before typing or pasting it, format the empty destination cells as Text. For a CSV, set the identifier column to Text in the import query; the worksheet cell format does not control Power Query types. Custom number format 00000 makes numeric 123 look like 00123 but leaves the stored number as 123. Formatting after zeros were removed cannot recover an unknown original width: 0007 and 00007 can both become numeric 7. For a known five-character code, =TEXT(A2,"00000") rebuilds that width; verify it against the source first.
In Google Sheets
Google Sheets support for this task has not been verified. Check the function, arguments and workflow in the target product before transferring the example. Matching formula notation does not establish identical behaviour. Nothing on this page is behind a login or a download: the exact answer is at the top, and the detail below it is there for when you need it. That is the whole promise here — the answer first, and just enough context to make it stick. For “stop excel from removing a leading zero”, keep the input cells and the result visible while you check the formula in Excel. Follow the worked example and change one input at a time. The steps explain the workbook calculation; this page does not run a live calculator for that task.
Common mistakes
- Formatting cells as Text after the data is already in, which does nothing — the zeros were lost on entry and the format change does not bring them back.
- Using Text format for values that later need to be summed or compared numerically.
- Reopening a CSV with automatic numeric conversion and assuming its identifiers will remain text. Import the identifier column explicitly as Text and check the source strings.
- Assuming CSV always writes only the underlying unpadded number. Excel can export displayed padded characters; CSV still carries no Excel number-format metadata or column type, and a later numeric import can remove the zeros again.
- Mixing padded text and unpadded numbers in one column so lookups match on some rows and fail on others.
Frequently asked questions
How do I add leading zeros in Excel?
Select the cells, press Ctrl+1, choose Custom and enter a format of 0s matching the width you want — 00000 for five digits. The cell stays a number and displays the padding.
Why does Excel delete my leading zeros?
Because the cell is formatted as a number, and numerically 00742 equals 742. Format the cells as Text before typing, or use a custom number format to pad the display.
How do I remove leading zeros?
If they are text, =VALUE(A2) converts back to a number, or multiply the column by 1 and paste the values back. If they come from a custom format, change the format to General.
How do I keep leading zeros when opening a CSV?
Use a typed import rather than relying on automatic conversion when reopening the file. On Windows desktop, choose Data > From Text/CSV > Transform Data, set the identifier column to Text and choose Replace Current if prompted. Check the source strings and any earlier conversion steps before Close & Load. Formatting the worksheet destination as Text does not set Power Query column types.
Other ways people ask this
This is also commonly searched as “how to stop excel from removing leading zeros”, “how to prevent excel from removing leading zeros csv”, “stop excel from removing leading zeros” and “how to stop excel from deleting zeros”. They describe the identical operation, so you are in the right place no matter how you phrased it.
Why do people search for this in so many different ways?
Because the same task has many names. “how to stop excel from removing leading zeros”, “how to prevent excel from removing leading zeros csv”, “stop excel from removing leading zeros” all point at the one operation explained on this page, which is why they all lead here.