VBA & Macro Problems

Find and Copy Complete Reference Codes from Word to Excel

Nimra MalikNimra Malik Nov 14, 2026 868 views

Question details

Find complete reference codes with a fixed prefix in a Word document and copy them into separate Excel rows.

Find and Copy Complete Reference Codes from Word to Excel
Product
Microsoft Word and Microsoft Excel
Device & OS
not provided
Scenario
A document contains many references in paragraphs, and the user wants to export the full codes for use in links to related PDF or spreadsheet files.
Observed behavior
The desired result is one complete reference, such as HMC01X001234-5678-1, per Excel row rather than the prefix alone.
Before you start

Check that your references follow a consistent pattern, including the number of digits in each section. The macro below requires desktop Word and Excel on Windows and searches the document's main text, not headers or footers.

Solution 1Recommended

Export complete reference codes with a Word VBA macro

Use this method to collect codes matching the example pattern into a new Excel workbook.

The regular expression HMC01X[0-9]{6}-[0-9]{4}-[0-9]+ matches the prefix, six digits, a hyphen, four digits, another hyphen, and one or more digits. Adjust it if your actual reference format differs.

The macro exports every match in document order, including duplicates. It does not create hyperlinks, and the pattern does not check what characters appear immediately before or after a match.

1
Open the VBA editor

Open the source document in desktop Word, press Alt+F11, and choose Insert > Module.

2
Add the export macro

Paste the following code into the module: Sub ExportReferenceCodes() Dim re As Object, matches As Object, match As Object Dim xl As Object, wb As Object, ws As Object Dim rowNum As Long Set re = CreateObject("VBScript.RegExp") re.Pattern = "HMC01X[0-9]{6}-[0-9]{4}-[0-9]+" re.Global = True re.IgnoreCase = False Set matches = re.Execute(ActiveDocument.Content.Text) Set xl = CreateObject("Excel.Application") xl.Visible = True Set wb = xl.Workbooks.Add Set ws = wb.Worksheets(1) ws.Columns(1).NumberFormat = "@" ws.Cells(1, 1).Value = "Reference code" rowNum = 2 For Each match In matches ws.Cells(rowNum, 1).Value = match.Value rowNum = rowNum + 1 Next match ws.Columns(1).AutoFit MsgBox matches.Count & " reference codes exported." End Sub

3
Run and check the export

Return to Word, press Alt+F8, select ExportReferenceCodes, and click Run. Compare several results in the new Excel workbook with the source document, especially if codes have variable formats.

4
Save the results

In the new Excel workbook, choose File > Save As and save the list as an Excel workbook.

Export complete reference codes with a Word VBA macro
Adjust the pattern before running: The example pattern is case-sensitive and may match part of a longer string. Revise it and verify the results if your codes use different lengths, lowercase letters, or additional suffixes.
Free Microsoft Office alternative

Review reference lists with WPS Office

WPS Office is a free, lightweight alternative for opening Word documents and working with exported reference lists in a familiar interface. Its compatibility with common Microsoft Office formats makes it easy to move documents and spreadsheets between suites; the Word-to-Excel VBA automation above is specific to Microsoft Office.

Review source documents and organize exported reference codes in spreadsheets.Open and edit common Microsoft Word and Excel formats, including DOCX and XLSX.Use familiar document and spreadsheet tools when moving from Microsoft Office.Work with a free, lightweight office suite.
microsoft office alternative - wps office

Frequently Asked Questions

How do I change the prefix or code length?

In the VBA editor, edit the re.Pattern line. Replace HMC01X with your prefix and change the digit counts, such as {6} and {4}, to match your reference format.

Will the macro search headers and footers?

No. ActiveDocument.Content.Text searches the main document text. References in headers, footers, or other document stories require additional VBA code.

Why are duplicate codes listed more than once?

The macro writes every match it finds. To keep one copy of each code, select the exported column in Excel and choose Data > Remove Duplicates.

How can I create links to the corresponding PDF files?

If the code is in A2 and each PDF is named after its code, enter a formula such as =HYPERLINK("C:\References\"&A2&".pdf",A2) in another column. Change the folder path and extension to match your files, then fill the formula down.