VBA & Macro Problems

How to Convert Date Formats Using a Word VBA Macro

Aamir Naveed Akram Oct 21, 2026 868 views

Question details

The user needs a way to automatically convert verbose ordinal date formats (e.g., 'the 12th of June') into a simplified day-month format (e.g., '12 June') using a Word macro.

How to Convert Date Formats Using a Word VBA Macro

Product: Word
Device & OS: not provided
Scenario: Standardizing date formats across a large document by automating the find and replace process with wildcard logic.
Observed behavior: The user requires the exact VBA wildcard syntax to identify ordinal numbers and the word 'of', replacing them while retaining the day and month values.

Before you start

Always test wildcard replacement macros on a copy of your document first to prevent accidental data modification, as wildcards can sometimes match unintended text patterns.

Solution 1Recommended

Update the VBA Wildcard Search and Replace Lines

Modify your existing macro's find and replace parameters to target ordinal numbers and the word 'of' using specific wildcard expressions.

Wildcards allow you to search for dynamic text patterns rather than exact words. By grouping specific parts of the search string with parentheses, you can retain those exact parts in your replacement text while discarding unwanted characters like 'th' and 'of'.

Open the VBA Editor

Press Alt + F11 to open the Microsoft Visual Basic for Applications window, and locate the module containing your date replacement macro.

Update the Find Text Parameter

Modify your find syntax to match the ordinal date pattern. Set the search text to: .Text = "the ([0-9]{1,2})[dhnrst]{2} of (" & myMonth(i) & ")"

Update the Replacement Text Parameter

Modify the replace syntax to output only the grouped day and month. Set the replacement text to: .Replacement.Text = "\1 \2"

Enable Wildcards

Ensure that the line .MatchWildcards = True is included within your With Selection.Find block so Word knows to interpret the special characters.

Run the Macro

Press F5 or click the Run button to execute the macro and reformat the dates in your document.

Update the VBA Wildcard Search and Replace Lines

Wildcard Breakdown: In the replacement string, \1 and \2 represent the first and second bracketed expressions from your search string. This keeps the day number and the month name while automatically dropping the ordinal suffix and the word 'of'.

Use WPS Writer for Advanced Document Macros and Wildcard Searches

WPS Writer provides comprehensive support for VBA macros and advanced wildcard find-and-replace features, allowing you to automate complex document formatting tasks with ease.

1. Open Document in WPS Writer: Launch WPS Office and open your text document.
2. Access the Developer Tools: Navigate to the Tools tab on the ribbon and click on Developer to access the VBA Editor.
3. Run Your Macro: Paste your wildcard macro code into a new module and click Run to automatically format your dates.

Fully compatible with Microsoft Word macro code and .docm file formatsRobust Find and Replace utility with extensive wildcard supportLightweight application that processes heavy documents and bulk replacements quickly

microsoft office alternative - wps office

Frequently Asked Questions

Why is my wildcard macro not finding any dates?

Ensure that the .MatchWildcards property is explicitly set to True in your VBA Find object. If it is omitted or set to False, Word will search for the literal characters (like brackets and braces) instead of interpreting them as wildcards.

What does [dhnrst]{2} mean in the wildcard search string?

This syntax instructs the search tool to look for exactly two characters ({2}) from the provided bracketed list (d, h, n, r, s, t). This perfectly matches all English ordinal suffixes: 'st', 'nd', 'rd', and 'th'.

Can I perform this date conversion without using VBA macros?

Yes. You can manually use the Advanced Find and Replace dialog (Ctrl+H). Check the 'Use wildcards' box, input 'the ([0-9]{1,2})[dhnrst]{2} of (*)' in the Find box, and '\1 \2' in the Replace box, then click Replace All.

Excel & Data Problems