03Feb 2015 by Charlie Nichols No Comments

How to Compare Two Strings in Excel for Similarity

Excel comes loaded with several useful text or string comparison functions. Out of the box, Excel provides a function named EXACT that will return whether two strings are exact matches. That’s great, unless you actually need to find strings that are really close or very similar. The good news is when you want to compare two strings in Excel you aren’t limited to exact matches only.

Using a function such as pwrSIMILARITY you can compare two strings and get a rating back (a percentage) that indicates the degree of similarity between the two strings. You actually have a great deal more flexibility with pwrSIMILARITY. For example, the EXACT function is case sensitive so comparing “Abc” and “abc” would return FALSE because of the capital letter. You can use the pwrSIMILARITY function and achieve the same result.

For example,

EXACT("Abc", "abc")

would return FALSE because of the mismatch. Similarly,

pwrSIMILARITY("Abc", "abc", FALSE)

would return 0% (or FALSE) because the third parameter is saying to be case sensitive (and is the default value). Alternatively,

pwrSIMILARITY("Abc", "abc", TRUE)

would return 100% (or TRUE) because the third parameter is saying to ignore the case of the letters.

Shades of Gray

pwrSIMILARITY is more useful especially when you aren’t looking for an exact match. In the image below you can see two columns of text and a column indicating the degree of string match between the first two columns.

The first two columns are made up of randomly generated text using the pwrRANDOMTEXT function. The third column compares the first two using the following formula.

pwrSIMILARITY(Table1[string1], Table1[string2])

You can see that the length of the strings need not be the same to determine the degree of similarity. You have much more flexibility in finding similar strings that need not be an exact match, but may be close. You can compare entire paragraphs if you wish, for what it’s worth.