Searching for same characters in two cells

[daved2424]

Hello

I am trying to work out a way of finding out if two cells have any characters in common.

For example, in cell A1 I have the string ABCDEF in it. In A2 I have ACEFG. Both cells have the characters A, C, E and F present and would therefore return TRUE.

Is there anyway to do this in a concise formula?

Many thanks for your help in advance

[Glenn Kennedy]

One way:

=IF(SUM(--ISNUMBER(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1),0)))>0,TRUE,"")

[daved2424]

Ooof! A bit longer than I was hoping for but at least it does the job.

Thank you very much!

[AliGW]

What does the length matter??? That's not long - it's pretty average in length, actually.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

[daved2424]

The length matters as it may not always be me maintaining it. Whilst I can understand what it is doing, someone with less experience has a much larger knowledge-gap to overcome to understand what the formula is doing.

Also, as any programmer will tell you, you should try to be efficient with your code. If this is as efficient as possible that's totally fine, it just has a few minor drawbacks.

[Glenn Kennedy]

Question. Are all the strings the same length, by any chance??

[Glenn Kennedy]

Here's a better alternative....

=IF(SUM(--ISNUMBER(MATCH(MID(A1,SEQUENCE(LEN(A1)),1),MID(B1,SEQUENCE(LEN(B1)),1),0)))>0,TRUE,"")

[Fluff13]

Another option

Please Login or Register  to view this content.

If it shouldn't be case sensitive (ie a=A) then change find to search