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.

12 Comments

    • Charlie

      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.

  • Robertas

    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?

    • Charlie Nichols

      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.

      • Robertas

        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?

  • Antonio Bonilla

    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

    • Charlie Nichols

      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%.

  • Antonio Bonilla

    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

Leave a Reply

Your email address will not be published. Required fields are marked *