Cell contents - Percentage match?

[JazzSP8]

Hey All

I'm after a method of comparing two cells and seeing how similar they are, preferably in a percentage format based on the words in the cell.

So;

A1 = "Apples" | B1 = "Apples" - The result would be 100%

A1 = "Apples" | B1 = "Banana" - The result would be 0%

A1 = "Apples Banana" | B1 = "Apples" - The result would be 50%

I'm thinking I might have to go the UDF route, but, thought I'd see if I'd overthought it first LOL

Thanks in advance for any help you can provide

 

[Gerald Higgins]

What other scenarios might there be ?

A1 = "Apples Banana Plum" | B1 = "Apples Orange Grape"
Result = ?

 

[JazzSP8]

What other scenarios might there be ?

A1 = "Apples Banana Plum" | B1 = "Apples Orange Grape"
Result = ?

Good question.

It's the number of matching words I'm after compared to the whole, using your example I'd be looking for a result of 33%

One word of three matches.

 

[Eric W]

A UDF might be preferable, if only for maintainability. But if you are looking at whole words within the string, then this formula might work for you:

	A	B	C
1	Apples	Apples	100%
2	Apples	Banana	0%
3	Apples Banana	Apples	50%
4	Apples Banana Plum	Apples Orange Grape	33%
5	Apples Orange Grape	Apples Orange	67%
6	Apples Banana Cherry Grape Tangerine	Banana Tangerine	40%
7	Apples	Grapes Pear Apples	100%
8	Apple	Apples	0%
9	apples	Apples	100%
10			100%

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Array Formulas Cell Formula C1 {=SUM(ISNUMBER(SEARCH(MID(" "&A1&" ",IF(MID(" "&A1&" ",ROW(INDIRECT("1:"&LEN(A1)+1)),1)=" ",ROW(INDIRECT("1:"&LEN(A1)+1)),NA()),FIND(" "," "&A1&" ",ROW(INDIRECT("2:"&LEN(A1)+2)))-ROW(INDIRECT("1:"&LEN(A1)+1))+1)," "&B1&" "))+0)/(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[JazzSP8]

Brilliant! That looks like it'll do the trick!

Many many thanks for that - Much appreciated

 

[Eric W]

Glad to help!