How to Find Similar Text in Two Excel Columns with Fuzzy Matching
Question details
The user needs to compare two Excel columns to identify near-exact matches, typos, missing words, or similar values.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing standardized database records against manually entered text to identify inconsistencies and partial matches.
- Observed behavior
- Standard duplicate checks fail to identify near matches, missing words, or slight typographical variations like DNC-2 and DNC-22.
Ensure your data in both columns is formatted as an official Excel Table (press Ctrl+T). This is a required step before loading your data into Power Query.
Use Power Query Fuzzy Merge
Power Query includes a built-in Fuzzy Merge feature that identifies similar, but not exactly identical, text values based on a customizable similarity threshold.
Fuzzy matching relies on an algorithm to compare text strings and assign them a similarity score. It is highly effective for catching spelling mistakes, prefix variations, or formatting inconsistencies.
Select your first formatted table, go to the Data tab, and click 'From Table/Range'. Repeat this step for the second table so both are loaded in the Power Query Editor.
In the Power Query Editor, navigate to the Home tab and click 'Merge Queries'.
Select your primary table from the top dropdown, and your secondary table from the bottom dropdown. Click on the column headers in both preview windows that you wish to compare.
Check the box that says 'Use fuzzy matching to perform the merge'. Expand the 'Fuzzy matching options' menu to adjust the Similarity Threshold. A score of 1.00 is an exact match; start around 0.80 for a balanced partial match, then click OK.
Calculate Edit Distance Using VBA
If you cannot use Power Query, calculating the minimum edit distance (Levenshtein distance) via a VBA script can assign a mathematical similarity score to two strings.
Need Advanced Data Analysis? Try WPS Office for Free
While Power Query is a specific Microsoft feature for advanced data merging, WPS Office provides a powerful, free, and lightweight alternative for your daily spreadsheet, documentation, and presentation needs. Enjoy seamless compatibility and a familiar interface without the hefty subscription fees.
- 1. Download WPS Office: Visit the official WPS website to download the free office suite for your operating system.
- 2. Install the Software: Run the lightweight installer to quickly set up the suite on your computer.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing Excel files directly with full formatting preserved.
Frequently Asked Questions
What is a similarity threshold in fuzzy matching?
The similarity threshold is a setting that determines how closely two text strings must match to be considered a pair. It ranges from 0.00 to 1.00, where 1.00 requires an exact match and lower numbers allow for more typos or differences.
Can I use standard Excel formulas for fuzzy matching?
Standard formulas do not natively perform fuzzy matching, but you can use wildcards (* and ?) with VLOOKUP or SEARCH functions to find basic partial text matches. For true fuzzy matching, Power Query or VBA is required.
Does fuzzy matching ignore uppercase and lowercase letters?
Yes, by default, Power Query's fuzzy matching is not case-sensitive. You can ensure this behavior by checking the 'Ignore case' option in the fuzzy matching settings.
Why did my fuzzy match pair completely unrelated words?
If your similarity threshold is set too low (e.g., below 0.50), the algorithm becomes too lenient and may match words that share only a few common characters. Increase the threshold score to fix this.
