Preserve Excel Leading Zeros in Word VBA Find and Replace
Question details
Preserve five-digit Excel formatting, including leading zeros, when a Word VBA macro replaces text in a table cell.

- Product
- Microsoft Word and Microsoft Excel
- Device & OS
- not provided
- Scenario
- A Word VBA macro reads find and replacement values from Excel cells and uses them in a Word table.
- Observed behavior
- Values displayed in Excel with a custom format such as 00000 lose their leading zeros when the macro reads the underlying values.
In Excel, check that the relevant cells display the intended five-digit values and that their columns are wide enough to show them. In Word, open the VBA editor and locate the code that reads the Excel cells and calls Find.Execute.
Read the displayed Excel values with the Text property
Use this approach when the macro should pass the values exactly as Excel displays them.
Excel stores a number separately from its display format. The Value property retrieves the underlying number, while Text retrieves the formatted display of a single cell.
In Word, open the VBA editor and find the assignments that read columns A and B from the Excel workbook.
Change the assignments to: from_text = wb.Sheets("1").Range("A" & oCell).Text and to_text = wb.Sheets("1").Range("B" & oCell).Text.
Keep passing from_text and to_text to the existing Find.Execute call, then run the macro and check the Word table for the expected leading zeros.

Apply a five-digit format in VBA before replacing text
Use VBA Format when both numeric values should be represented with at least five digits, regardless of their Excel display format.
Work with formatted spreadsheets and documents in WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for working with spreadsheets and documents. Open compatible Office files to make a straightforward transition, but test VBA automation separately because WPS does not fix the Word VBA macro.

Frequently Asked Questions
Why does Excel show leading zeros that VBA does not read?
A custom number format changes the cell's display, not its stored numeric value. Reading Range.Value returns the underlying number; reading a single cell with Range.Text returns its displayed text.
Should I use Text or Format for the replacement values?
Use Range.Text when you want the Excel cell's displayed formatting. Use Format(value, "00000") when the macro should explicitly apply a minimum five-digit format before calling Find.Execute.
Why does the Text property return number signs?
Excel may display number signs when a column is too narrow. Widen the column in the worksheet and confirm the value is visible before running the macro.
Will the 00000 format shorten a six-digit number?
No. VBA Format(value, "00000") pads numbers shorter than five digits but leaves additional digits intact.




