Compare two cells in excel and return the matching number of Characters

Question

I'm looking for an Excel function, which compares two cells and returns the number of matching characters between those two cells.

I want the function to ignore case, i.e. A = a, B = b, etc.

Example:

A1 = abcdef and B1 = Afcblm.. C1 = 4 (number of matching characters = 4 (afcb)

The function should compare all characters in two cells and then return every character which is present in both cells regardless of the order.

I have created the function below

=SUM(IFERROR(IF(MID(A1, ROW(INDIRECT("$1:$" & LEN(A1))), 1)=MID(B1, ROW(INDIRECT("$1:$" & LEN(B12))), 1), 1, 0), 0))

It does not work when I apply this to above example C1 = 1 and I believe this issue is due to this function comparing characters sequentially.

Can A1 contain multiple instances of the same letter? e.g. A1=abacdef? — Ben, Jan 03 '17 at 11:38
There;'s a typo in the formula above - says Len(B12) instead of LEN(B1). But it will still only compare letters which are in the same position, e.g. axbx and ayby would give 2 but abcd and dcba would give 0. — Tom Sharpe, Jan 03 '17 at 14:06

CallumDA · Answer 1 · 2017-01-03T14:14:01.893

2

this will work if entered using Ctrl+Shift+Enter, rather than just Enter

=SUM(IF(ISERROR(FIND(MID(A1,ROW(INDIRECT("$A$1:$A$"&LEN(A1))),1),B1)),0,1))

You will know if it's been entered correctly because curly braces will be placed either side of the formula in the formula bar like this: {=...}

It works by breaking the text in cell A1 into individual letters

a
b
c
d
e
f

it then performs a FIND to check if the letters can be found in cell B1, returning 1 or 0

And finally summing the array completes the process and returns 4 as required

Update as per Tom Sharpe's suggestion - for case insensitivity use SEARCH instead of FIND:

=SUM(IF(ISERROR(SEARCH(MID(A1,ROW(INDIRECT("$A$1:$A$"&LEN(A1))),1),B1)),0,1))

edited Jan 03 '17 at 14:14

answered Jan 03 '17 at 11:34

CallumDA

Nice answer. OP may want to change FIND to SEARCH to ignore case. – Tom Sharpe Jan 03 '17 at 14:11
Thanks - I updated the answer to include this suggestion – CallumDA Jan 03 '17 at 14:14

Tom Sharpe · Answer 2 · 2017-01-03T18:29:51.557

Just for completeness this would be another way of doing it. It forces each letter in A1 to be compared against all letters in B1.

 =SUM(IFERROR(IF(MID(A1, ROW(INDIRECT("$1:$" & LEN(A1))), 1)=TRANSPOSE(MID(B1, ROW(INDIRECT("$1:$" & LEN(B1))), 1)), 1, 0), 0))

If you wanted to find how many distinct letters were in both cells you could use this

=SUM(ISNUMBER(SEARCH(MID("abcdefghijklmnopqrstuvwxyz",ROW($1:$26),1),A1))*ISNUMBER(SEARCH(MID("abcdefghijklmnopqrstuvwxyz",ROW($1:$26),1),B1)))

Both of them are array formulae and have to be entered with CtrlShiftEnter

2 Answers2