How can I identify a common character in 2 Strings in Excel?-CodePudding

Suppose I have ABC in cell A1 and XaA in cell B1. Taking into account upper and lower case I would like to recognize that A is indeed in common. How can I do this using Excel or Google Sheets function?

Thank you

I used =INDEX(FLATTEN(FILTER(FLATTEN(FILTER(REGEXEXTRACT(TO_TEXT(E1), REPT("(.)", LEN(E1))),REGEXEXTRACT(TO_TEXT(E1), REPT("(.)", LEN(E1)))<>"")), FLATTEN(FILTER(REGEXEXTRACT(TO_TEXT(E1), REPT("(.)", LEN(E1))),REGEXEXTRACT(TO_TEXT(E1), REPT("(.)", LEN(E1)))<>""))<>"")&""&TRANSPOSE(FILTER(FLATTEN(FILTER(REGEXEXTRACT(TO_TEXT(E2), REPT("(.)", LEN(E2))),REGEXEXTRACT(TO_TEXT(E2), REPT("(.)", LEN(E2)))<>"")), FLATTEN(FILTER(REGEXEXTRACT(TO_TEXT(E2), REPT("(.)", LEN(E2))),REGEXEXTRACT(TO_TEXT(E2), REPT("(.)", LEN(E2)))<>""))<>"")))) formula but clearly I am not moving in the right direction.

CodePudding user response：

Use regexmatch() and regexextract(), like this:

=regexmatch( B1, join( "|", unique( transpose( regexextract(A1, rept("(.)", len(A1))) ) ) ) )

CodePudding user response：

another approach... (handy if you have characters that needs to be escaped in regex)

if you untangle each:

=REGEXEXTRACT(A20, REPT("(.)", LEN(A20)))

then you can filter it:

=FILTER(C20:E20, COUNTIF(C21:E21, C20:E20))

in one go:

=LAMBDA(a, b, FILTER(a, COUNTIF(b, a)))(
 REGEXEXTRACT(A20, REPT("(.)", LEN(A20))), 
 REGEXEXTRACT(B20, REPT("(.)", LEN(B20))))