Sometimes you have a need to compare text strings that don’t exactly match. You might need to match “(425)555-1212” to “4255551212” for example. Or perhaps you’d like to match “12345 Main st” to “12345 main street”. The Excel PowerUps Premium Suite add-in (available as a free trial download) includes a function that helps you do just that by enabling a fuzzy search in Excel.
Fuzzy Search and Replace
If you want to do a fuzzy search and replace you can use the Fuzzy Find and Replace tool. You can see an example of it’s use here.
Fuzzy Search in Excel with a New Function for your Formulas
The function is called pwrSIMILARITY. It simply compares the two text strings and returns a percentage value that represents how similar the two values are. If they are a total match, the value is 100%. If they’re not a match at all, you get 0%. You can choose between case-sensitive or case-insensitive comparisons.
The function call looks like the following:
=pwrSIMILARITY(A2,B2)
In the examples above, you wind up with the following when using this Excel add in.
Do you need to do a fuzzy VLOOKUP? Check out Fuzzy VLOOKUP in Excel.
There you go.
[…] Fuzzy search in Excel to find similar text values in Excel […]