Find and Copy Complete Reference Codes from Word to Excel
Question details
Find complete reference codes with a fixed prefix in a Word document and copy them into separate Excel rows.

- 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.
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.
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.
Open the source document in desktop Word, press Alt+F11, and choose Insert > Module.
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
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.
In the new Excel workbook, choose File > Save As and save the list as an Excel workbook.

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.

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.




