Conditional formatting by matching part characters of cell with another cell

Ref attachment TEST222.xlsx

In sheet "TWO" conditional formatting is used to match last 5 characters of A column with sheet "ONE"'s A column's last 5 characters.

But I need to match last 5 characters of A column with sheet "ONE"'s A column's "any five characters".

for example when looking for 53246 it should highlight 18231053246 as well as 53246182302.

How to do this. Thanks in advance.

Try this as teh CF rule instead...
=MATCH("*"&RIGHT(A1,5)&"*",ONE!$A$1:$A$251,0)

Also I require one more solution, i forgot to mention.

I need to find and highlight "exact matching" for those cells too. Please help on this also.

Originally Posted by tim201110

=sumproduct(--isnumber(find(mid(one!a1,row(indirect("1:"&len(one!a1))),1),two!$a$1:$a$3144)))>=5

i think it is not working.

Originally Posted by jilaba

Also I require one more solution, i forgot to mention.

I need to find and highlight "exact matching" for those cells too. Please help on this also.

As of now, i need to get solution for this.

did you try post 2?

Originally Posted by FDibbins

did you try post 2?

I saw this now only. It is working great. Thanks. Also I need to find and highlight "exact matching" for those cells too. Please help on this also.

To 'exactly match' the entire string of numbers modify Ford's formula so that it reads: =MATCH(A1,ONE!$A$1:$A$251,0)
Let us know if you have any questions.