Solved Possible Formula to match similar characters in verious colm

August 20, 2012 at 11:01:17
Specs: Windows XP
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,


See More: Possible Formula to match similar characters in verious colm

Report •

#1
August 20, 2012 at 12:06:35
✔ Best Answer
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		@345f

If 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.


Report •

#2
August 20, 2012 at 13:19:55
For a simpler formula use:

=FIND($A$1,$B1,1)

MIKE

http://www.skeptic.com/


Report •

#3
Report •
Related Solutions


Ask Question