excel-formulatextoffice365text-extraction

how can I compare to texts and extract only the difference text into the other cell


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.


Solution

  • 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)
    )
    

    Result