VBA & Macro Problems

Preserve Excel Leading Zeros in Word VBA Find and Replace

Khadija KhanKhadija Khan Nov 20, 2026 868 views

Question details

Preserve five-digit Excel formatting, including leading zeros, when a Word VBA macro replaces text in a table cell.

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

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.

Solution 1Recommended

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.

1
Open the macro

In Word, open the VBA editor and find the assignments that read columns A and B from the Excel workbook.

2
Replace Value with Text

Change the assignments to: from_text = wb.Sheets("1").Range("A" & oCell).Text and to_text = wb.Sheets("1").Range("B" & oCell).Text.

3
Run the replacement

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.

Read the displayed Excel values with the Text property
Check the Excel display: The Text property reflects what Excel displays. If a column is too narrow and shows number signs instead of the value, widen it before running the macro.
Free Microsoft Office alternative

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.

View and edit spreadsheet number formats, including leading-zero formatsOpen and edit Microsoft Excel XLSX and Word DOCX filesUse a familiar interface for everyday document and spreadsheet tasksMove existing Office files into a lightweight, free office suite
QA img-9

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.