Hello,

I am trying to figure out a possible formula in Excel that can match similar characteristic. For example, I have two columns, Column "a" & "b". Column "a" has 300 cells with various number and alpha characteristic (ie. A596). Column "b" has 1000 cells with again various number and alpha characteristic but column "b" may have subtle differences in the number/alpha scheme(ie. 0A596, @A596). I would like the formula to return a matches of these subtle differences, so column "a" character "A596" matches/finds 5 similarities in column "b" (ie. 0A596, @A596, A596R, #A596, AA596).Any suggestions would be great appreciated,

Let's say you start with this in A1:B10: A B 1 A596 0A596 <-- 2 R4345 3 A596R <-- 4 #3214 5 AA596 <-- 6 4564T 7 @A596 <-- 8 3E3E3 9 #A596 <-- 10 @345fIf you Select B1:B10 and use this Rule in Conditional Formatting, cells B1, B3, B5, B7 and B9 will be highlighted, as noted by the arrows above.

=OR(RIGHT(B1,4)=$A$1,LEFT(B1,4)=$A$1)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think Jony Ive could make a big impact on Airbnb?

Discuss in The Lounge

Poll History