VBA & Macro Problems

How to Find Exact Word and Combination Matches in Excel Lists Using VBA

Jason Miller Oct 23, 2026 868 views

Question details

The user needs a VBA procedure to split sentences, find exact word matches against a predefined list, and output the grouped combinations across multiple columns while avoiding false positive wildcard matches.

How to Find Exact Word and Combination Matches in Excel Lists Using VBA

Product: Excel
Device & OS: not provided
Scenario: Extracting and grouping specific words from a dataset based on an 'ORIGIN' named range, ensuring single letters like 'I' or 'A' are not incorrectly matched as substrings.
Observed behavior: Standard search or wildcard methods incorrectly match single characters inside other words, failing to isolate exact word matches and properly distribute combinations across columns.

Before you start

Before running the macro, ensure you have selected your target reference list and defined it as a named range called 'ORIGIN' in your workbook.

Solution 1Recommended

Use a VBA Macro to Split Strings and Find Exact Matches

Create a custom VBA script that splits cell contents into an array of words, directly compares them to the 'ORIGIN' range to ensure an exact match, and writes the combinations across the designated columns.

To avoid false positives from wildcard searches, the best approach is to break down the sentences into individual words first. Once separated, each word is checked against your reference list using exact equality.

After validating the matches, the code can dynamically assign the results to column C and group subsequent combinations across columns D through N.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor. Click 'Insert' from the top menu and select 'Module' to create a blank script window.

Write the Split and Match Logic

Define a Sub procedure. Use the VBA 'Split()' function to break the target sentence string into an array using a space as the delimiter. Loop through this newly created array.

Compare Against the ORIGIN Range

Inside your loop, iterate through the cells in 'Range("ORIGIN")'. Compare the split word to the cell value using the exact equals operator (=). Do not use the 'Like' operator or asterisks (*), as this prevents single letters like 'A' or 'I' from incorrectly triggering a match.

Output and Group Combinations

When an exact match is confirmed, instruct the macro to write the matching word to Column C. Group the matched values by checking the preceding column's identifier, and use an offset to distribute the remaining combinations across columns D through N.

Use a VBA Macro to Split Strings and Find Exact Matches

Avoid Wildcard False Positives: By comparing exact array items rather than searching the entire string, you guarantee that single-letter words only match when they stand alone in the sentence.

Run VBA Macros Seamlessly in WPS Spreadsheet

WPS Office provides excellent support for VBA and macros, allowing you to execute complex data extraction, split strings, and run exact match scripts just as you would in Microsoft Excel.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the 'ORIGIN' named range.
2. Access the VBA Editor: Navigate to the 'Tools' tab on the ribbon and click on 'Macro', or simply press Alt + F11 to open the built-in VBA editor.
3. Insert Your Code: Right-click your workbook in the Project Explorer, select 'Insert' > 'Module', and paste your exact word matching VBA code.
4. Run the Macro: Save the file as a Macro-Enabled Workbook (.xlsm) and press F5 or click 'Run' to process your sentences and output the column combinations.

Fully compatible with Microsoft Excel .xlsm and .xlsb macro formatsBuilt-in VBA editor for writing, debugging, and running scripts seamlesslyLightweight, fast execution for large datasets and array processingFree to use for everyday advanced office tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel search incorrectly find single letters like 'A' or 'I'?

This happens if your formula or VBA macro uses wildcard matching (such as wrapping terms in asterisks like '*A*'). Wildcards look for the character anywhere in the text, meaning the letter 'A' inside the word 'Apple' will trigger a false match. Using exact string comparison fixes this.

How do I split a sentence into individual words in VBA?

You can use the built-in 'Split()' function in VBA. By passing your sentence string and specifying a space character (' ') as the delimiter, the function generates a zero-based array containing each isolated word.

What is a named range and how is it used in macros?

A named range assigns a custom, readable name (like 'ORIGIN') to a specific block of cells. In VBA, you can easily reference these cells by looping through 'Range("ORIGIN")' without needing to hardcode specific cell addresses like 'A1:A50'.

Excel & Data Problems