VBA & Macro Problems

Preserve Leading Zeros in Excel VBA Find and Replace

Steven ParkerSteven Parker Nov 22, 2026 868 views

Question details

Preserve leading zeros in cell values when an Excel VBA macro removes text.

Preserve Leading Zeros in Excel VBA Find and Replace
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.
Before you start

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 ###.

Solution 1Recommended

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.

1
Select the target cells

In Excel, select the contiguous range to process. Exclude formulas and make the columns wide enough to display every value in full.

2
Add the macro

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

3
Set the text to remove

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.

4
Run the macro

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.

Capture displayed values and replace text as strings in Excel VBA
Displayed values become text: This method uses the displayed value rather than the underlying numeric value. It cannot recover zeros that are no longer present or displayed, and it may change how dates or other formatted values are stored.
Work with text values in WPS

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. 1. Open the workbook: Open the Excel workbook in WPS Spreadsheets and locate the column containing the text values.
  2. 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. 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. 4. Fill and review: Drag the formula's fill handle down the column and check that the results retain their leading zeros.
Preserves leading zeros in text values while removing specified charactersOpens and saves Microsoft Excel XLSX filesUses a familiar spreadsheet formula without requiring a macroLets you review formula results before replacing the original values
microsoft office alternative - wps office

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.