Preserve Leading Zeros in Excel VBA Find and Replace
Question details
Preserve leading zeros in cell values when an Excel VBA macro removes text.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user runs a VBA find-and-replace macro on cells containing values such as 002.
- Observed behavior
- The replacement converts values to numbers, causing leading zeros to disappear.
Select a contiguous range of cells containing values, not formulas, and widen the columns so the complete values are visible. This method captures what Excel displays, so check that no cells show truncated values or ###.
Capture displayed values and replace text as strings in Excel VBA
Use this method when the displayed leading zeros must remain after text is removed.
The macro captures each cell's displayed text before applying the Text number format, then replaces the specified text and writes the result back. This also preserves zeros displayed by a custom number format, provided the full value is visible.
The results are text values, not numbers. The macro overwrites the selected cells, so select only cells whose displayed values you want to keep.
In Excel, select the contiguous range to process. Exclude formulas and make the columns wide enough to display every value in full.
Open the VBA editor with Alt+F11, choose Insert > Module, and paste this code: Sub ReplaceKeepingZeros() Dim r As Range, c As Range Dim values() As String Dim i As Long Set r = Selection ReDim values(1 To r.Cells.Count) i = 1 For Each c In r.Cells values(i) = c.Text i = i + 1 Next c r.NumberFormat = "@" i = 1 For Each c In r.Cells c.Value2 = VBA.Replace(values(i), "remove", "", 1, -1, vbBinaryCompare) i = i + 1 Next c End Sub
In the VBA editor, replace "remove" in the VBA.Replace call with the exact text to remove. The empty string after it means the matching text will be deleted.
Return to Excel, select the target range, and use Developer > Macros to run ReplaceKeepingZeros. Check that values such as 002 remain displayed with their zeros.

Remove Text Without Losing Zeros in WPS Spreadsheets
Use a SUBSTITUTE formula on values stored as text to remove unwanted characters while keeping their leading zeros.
- 1. Open the workbook: Open the Excel workbook in WPS Spreadsheets and locate the column containing the text values.
- 2. Check the source values: Select a source cell and check that its value, such as 002, is stored as text. If zeros appear only because of a numeric format, convert the displayed values to text first.
- 3. Enter the replacement formula: In an empty cell next to the first value, enter =SUBSTITUTE(A2,"remove",""). Change A2 to the source cell and "remove" to the text you want to delete.
- 4. Fill and review: Drag the formula's fill handle down the column and check that the results retain their leading zeros.

Frequently Asked Questions
Why does Excel remove leading zeros during a VBA replacement?
If a replacement result is interpreted as a number, Excel stores 002 as 2. Format the destination cells as Text and write the replacement results as strings.
Can I recover zeros after Excel has converted 002 to 2?
Not from the numeric value alone. If the required width is known, apply a custom format such as 000 through Format Cells > Number > Custom, or use a formula such as =TEXT(A2,"000") to produce text.
Why does the macro capture cell text before changing the number format?
A custom number format may be what displays the leading zeros. Changing the format to Text first can remove that display, so the macro reads each cell's displayed text before changing the format.
Will this macro preserve formulas in the selected range?
No. It writes the displayed results back as text values, replacing any formulas. Select only cells whose formulas do not need to be retained.




