PowerUp Description
The pwrSIMILARITY PowerUp function returns a percentage rating of the comparison of two strings in Excel. This comparison can either be case sensitive (the default) or case insensitive.
PowerUp Syntax
pwrSIMILARITY(value1, value2, [ignorecase], [sensitivity])
The pwrSIMILARITY PowerUp function has the following arguments:
value1 – Required. This one of the text strings to be compared.
value2 – Required. This is the other text string to compare.
ignorecase – Optional. The default value is FALSE, meaning the function will consider letters of different case to not match.
sensitivity – Optional. Specifies the degree of sensitivity in the text comparison. The values range from 2 thru 6. Use a higher value for larger blocks of text. The default value is 3.
Return Value
pwrSIMILARITY will return the percentage match between the two strings provided.
Remarks
The default comparison is case sensitive.
Increase the value of the sensitivity parameter when comparing large blocks of text. For shorter strings, consider using the lower sensitivity threshold of 2.
Examples
=pwrSIMILARITY("The Excel Text Analyzer add-in is awesome!!", "The Excel Text Analyzer add-in rocks!")
Will return a percentage value (between 0 and 1.0).
=pwrSIMILARITY("The Excel Text Analyzer add-in is awesome!!", "THE EXCEL TEXT ANALYZER ADD-IN ROCKS!", TRUE, 4)
Will do a case-insensitive comparison, with a higher sensitivity threshold – meaning the strings must be an even closer match to score higher. The return value will be a percentage score.
[…] pwrSIMILARITY […]
What if I don’t want a percent value, I just want the word “duplicate”, if it finds a similar match. What do I put in the formula?
Try something like this:
=IF(pwrSIMILARITY(string1, string2)>.5,"duplicate","different")The .5 value is somewhat arbitrary. You may find you want to make it a bit higher (will always be between 0 and 1) to get fewer potential false positives.
[…] pwrSIMILARITY […]
What is the method of similarity does pwrSimilarity uses to match strings – Jaccard, Cosine etc.
Hi,
Is there a way to use this function for a range of cells?
I mean that i have to separate Sheets, Example:
Sheet 1. Sheet 2.
A B A B
Panda 5 Appl
Horse 7 Pand
Apple 8 orse
What I need to do, is to make a formula for YES or NO in sheet 2 collumn B if any cell in collumn A in sheet 1 contains similair text to collumn A in sheet 2. Is there a way to do that?
I’m not sure I understand how your data is set up. But if you’re trying to do a fuzzy match of a value to a range you might try the pwrMATCH function. For example, you might use =pwrMATCH(A1,Sheet1:A1:A3,50) to get a fuzzy match on the contents of cell A1 with one of the first 3 cells in Sheet1.
I believe your answer is what i wanted, thank you, but there is one problem – my excel can not find functions like pwrMATCH or pwrSIMILARITY? and I’m doing it in google sheets, should i update something or install some addons?
I´m evaluating this add for excel, I’ve stuck with pwrSIMILARITY function including two cells insted “strings” as the example above always return value 0, no matter the text contained in cells: =pwrSIMILARITY($B$1351, B1352)
Where b1351 = SALES and B1352 = Salesman
Thanks for your help
Antonio Bonilla
Hi, the issue is the default comparison is case sensitive. If you change the function to =pwrSIMILARITY($B$1351, B1352, 1) it will ignore the text case and return a value of 0.5 or 50%.
Hi, everything is ok, I read carefully the properties of the function. My trouble was the language; TRUE in my spanish excel version es VERDADERO, silly isn’t?
Greetings
Hi, it’s no problem. You can use a zero (0) or one (1) to indicate true/false too. 0 = false, and 1 = true.