compare two cells for controlled similarity

[richx]

Hi all,

how would I write a formula that can compare two cells and return true if 75% or more of the characters in the cells match in order.

for example,
cell B2 has length 24: "The cat in the black hat"
cell C2 has: "4444cat in the black hzzzzdsaf"

this would be considered similar because 18 of the 24 characters from B2 were present in C2, even with all the random text. however, "The cat in the blue hat" would not be similar even though 75% of original text is present, since there is a break in original string where black is swapped for blue. But, "The cat in the blank basket" would be considered similar since exactly 18 of the characters are the same and appear in the same order.

My plan was to use find() for 75% of the characters in B2 starting at char1 and look in C2. Then starting at char2 and etc. But is there a more efficient way to do this?
Like for my example, the formula would search for "The cat in the b" in C2, then search for "he cat in the bl", then "e cat in the bla" etc.

Creating a new function in VBA would be fine also. Thanks

[Richard Buttrey]

Hi,

You might like to try the Excel Fuzzy Lookup Add-In here...
https://www.microsoft.com/en-gb/down....aspx?id=15011

I've used it occasionally for these sorts of things.