Function Problems

How to Extract Unique Pattern Values in Excel 2013

Phi Hung Vo Oct 7, 2026 871 views

Question details

The user needs a method to search across multiple cells and extract unique string values that match a specific formatting pattern (Nxx.NPyyy) in Excel 2013.

Extracting Unique Nxx.NPyyy Pattern Values in Excel 2013

Product: Microsoft Excel 2013
Device & OS: not provided
Scenario: Extracting distinct text patterns from a dataset where modern dynamic array functions are unavailable.
Observed behavior: Modern formulas using FILTER, LET, TEXTSPLIT, or REDUCE return errors because Excel 2013 does not support dynamic arrays or newer text-processing functions.

Before you start

Ensure you have enabled the Developer tab in Excel to use VBA macros, and save a backup copy of your workbook before running any scripts.

Solution 1Recommended

Use a VBA Macro with Regular Expressions

Since Excel 2013 lacks native pattern-matching functions, using a VBA macro combined with VBScript Regular Expressions is the most effective way to extract unique values matching your Nxx.NPyyy pattern.

VBA allows you to utilize VBScript's regular expression engine to match complex text patterns. By storing these regex matches in a Scripting Dictionary object, you can easily filter out duplicate entries and output a clean list of unique values.

Open the VBA Editor

Press Alt + F11 to open the Microsoft Visual Basic for Applications (VBA) Editor, then click Insert > Module from the top menu.

Enable Required References

In the VBA Editor, go to Tools > References. Scroll down to check 'Microsoft VBScript Regular Expressions 5.5' and 'Microsoft Scripting Runtime', then click OK.

Write the Regex Pattern Script

Create a macro that sets a RegEx object with the pattern 'N\d+\.NP\d+'. Set the Global property to True so it searches the entire cell content.

Filter Duplicates with a Dictionary

Loop through the selected cells and execute the regex search. For each match found, add the value to a Dictionary object as a key. The Dictionary inherently rejects duplicate keys.

Output and Run

Write a final loop to output the Dictionary keys to a target column. Close the VBA Editor, select your data, and run the macro from Developer > Macros.

Use a VBA Macro with Regular Expressions

Macro Security: Remember to save your file as an Excel Macro-Enabled Workbook (.xlsm) so you do not lose your VBA code upon closing.

Solution 2

Extract Patterns Using Power Query

If you prefer avoiding VBA, you can use the Power Query add-in for Excel 2013 to split text and remove duplicates, though it requires creative filtering instead of pure regex.

Free Microsoft Office alternative

Upgrade to WPS Office for Modern Spreadsheet Features

Struggling with the limitations of outdated software like Excel 2013? Switching to WPS Office provides a lightweight, highly compatible alternative with modern updates to help you manage and analyze data efficiently without expensive subscriptions.

Fully compatible with Microsoft Excel formats (.xlsx, .xlsm, .csv).Includes modern data processing features to make complex data extraction easier.Completely free to use with a lightweight installation package.Familiar tabbed interface ensures a seamless transition with zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a #NAME? error when using FILTER or LET in Excel 2013?

The #NAME? error occurs because dynamic array functions like FILTER, LET, and TEXTSPLIT were introduced in Microsoft 365 and Excel 2021. Excel 2013 does not recognize these newer function names.

Can I use Regular Expressions directly in Excel 2013 formulas?

No, Excel 2013 does not have native regular expression functions. You must use a VBA macro utilizing the VBScript Regular Expressions library to perform regex pattern matching.

How do I easily remove duplicates in Excel 2013 without formulas?

You can use the built-in Remove Duplicates feature. Select your data range, go to the Data tab on the ribbon, and click 'Remove Duplicates' in the Data Tools group.

Excel & Data Problems