Match Left 14

[Utradeshow]

Hi All,

I am trying to do a match formula to trigger a conditional format.

In column A, I need to match the LEFT 14 characters and highlight if TRUE.

I have serial numbers with a Letter at the end. Sometimes they are missing the 15th letter so I need to isolate them.




I have this formula that doesn't seem to be working.
=SUMPRODUCT(--(LEFT($A$1:$A$1,14)=LEFT(A1,14)))>1

 

[MountainFog]

Can you provide a screenshot of your data? Or provide sample data?

 

[Utradeshow]

Book1
	A
1	SERIAL
2	DGH12458796321
3	DGH12458796357
4	XYZ12345678900M
5	XYZ12345678900
Sheet1

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

How about

=AND(A2<>"",COUNTIFS(A$2:A$100,LEFT(A2,14)&"*")>1)