highlight cells that contain the same first 5 characters

try this
=(COUNTIF($A$3:$A$30,LEFT(A3,5)&"*")>1)

see attached

I tried the formula that etaf provided, but it didn't work for me...Here's a larger example of data if it would help.

LI007-278936588
IS139-278806003
IS139-279000332
IS141-278781674
IS141-278781678
IS141-278781680
IS023-278781687
IS023-278781690
IS001-278781692
IS489-278781695
IS976-278781696
IS141-278781698
LI007-279005411
ZB436-279019742
ZB436-279028342
ZB437-279447040
RA085-278751643
RA088-279464874
RA085-279490191
AM099-279473098
AM120-279118094

as requested a sample spreadsheet now

I added the extended data into my example and reloaded here

I found a solution that seems to work best for what I'm looking for. I decided to just separate the data with text to columns and use a countif formula =COUNTIF($A$1:$H$100,A1)>3 in the conditional formatting use a formula option to show me specifically which cells contain the same 5 characters that appear 4 or more times.

I didn't previously ask about the specific number of occurences. I would have been happy with just showing duplicates, but the more specific the better

Thank you all for all your help!

Does that work - as its looking at the whole string and not the 1st 5 characters
=COUNTIF($A$1:$H$100,A1)>3

as post #2
my formula is the same
=(COUNTIF($A$3:$A$30,LEFT(A3,5)&"*")>1)
only it looks at the 1st 5 characters

so changing to the same ranges as yours
=(COUNTIF($A$1:$H$100,LEFT(A1,5)&"*")>1)

you are also comparing A1 across columns A to H ?

Hi benishiryo,

Attached is a new WB.

you need to explain what you are trying to do - with a lot more detail please

Hi etaf,

benishiryo was assisting me in creating a formula that will be used in conditional formatting to highlight only dates that are the same and appear consecutevly 4 or more times.

you have started a new thread - so you probably need to reply to the post where you are already getting help and not start a new thread here

so here
http://www.excelforum.com/excel-form...25#post3200725

I was asked to start a new thread so I did. If I shouldn't have then I'm sorry.

no , if you are starting a new thread - then you do need to give all the supporting information and detail for what you would like to achieve as new people will not know what you want to now achieve

=COUNTIF($A$1:$A$200,A1)>4

so A is the column you have the dates change that to the column with the dates and the range - not sure which columns A, L or M