Check first 5 Char in text field

[dave46049]

Folks, I have two columns of data that have text in - lets say the text reads "12345acbdefghigj" - ovviously the numbers and char change for each row.

Is there a function which can compare the first 5 characters of data in each column and return a true / false flag [ or the first 5 char of data?

Thanks

 

[VoG]

Try

=LEFT(A1,5)=LEFT(B1,5)

 

[dave46049]

I need to check whether the first five characters are in ANY row in column B

So I guess I'm looking for something like =LEFT(A1,5)=LEFT(B1-B500,5)

 

[VoG]

Try

=SUMPRODUCT(--(LEFT(B1:B500,5)=LEFT(A1,5)))>0

 

[T. Valko]

I need to check whether the first five characters are in ANY row in column B

So I guess I'm looking for something like =LEFT(A1,5)=LEFT(B1-B500,5)

Maybe this...

=COUNTIF(B:B,LEFT(A1,5)&"*")

 

[dave46049]

Biff - where does this one reference the first 5 char of columnB?

 

[T. Valko]

Biff - where does this one reference the first 5 char of columnB?

In the criteria:

LEFT(A1,5)&"*"

Book1
	A	B	C
1	12345xx	asdfghj	4
2	_	12345zx	_
3	_	dfghytu	_
4	_	12345ui	_
5	_	12345oop	_
6	_	12346we	_
7	_	qwertyu	_
8	_	uioklpp	_
9	_	ercvbnh	_
10	_	12345xx	_
Sheet1

Formula entered in C1:

=COUNTIF(B:B,LEFT(A1,5)&"*")