Column A | Column B | Column C |
---|---|---|
Iam18yearsold | Iam17yearsold | 7 |
thereisagirl | therearegirls | are,s |
I need to compare to cells and then extract only the difference to the third cell. I want to have the result like in column C and would be great if I do not need to use VBA, I use microsoft 365. Please kindly help.
I tried with substitute but it did not work because it return the whole text that difference. =Substitute(A2;B2;"") the result Iam17yearsold. But I only want 7 for the answer.
Assuming TAB, char(8), is not part of your texts to compare, using that as a delimiter:
=LET(
extract_diff, LAMBDA(a, b,
LET(
idx, SEQUENCE(, MAX(LEN(a), LEN(b))),
b, MID(b, idx, 1),
delim, CHAR(8),
ARRAYTOTEXT(
TEXTSPLIT(CONCAT(IF(MID(a, idx, 1) <> b, b, delim)), delim, , TRUE)
)
)
),
MAP(A2:A3, B2:B3, extract_diff)
)