Hi there,
I copied the formula u suggested in B1.. It gives an error :-(
By the way what is the W1 in the right hand side of the formula?

Rashid

"keepITcool" wrote in message
...
Rashid,
i think you can forget the VBA..

since you're NOT requiring an exact match
THIS simple function should do..

=SUMPRODUCT(--(NOT(ISERROR(FIND($AA$1:$AA$25;W1)))))

if it's 0 = no occurance..
if higher the number of matches found :)

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Rashid Khan" wrote:

Hello Mark,
Sorry once again for the confusion on my part.. The String is
Mahadeoguru .. thats why it is extracting Mahad from it.. as it is
preceded by a space.

Can this u give u some idea.
Rashid
"Mark Thorpe" wrote in message
...
Note that in the line:

ElseIf InStr(sFullName, " " & Cells(iRowAA, 27).Value) 0

we are searching for the value from column AA preceded by a space.
(thus the " " &)

For example, it will not find "mahad" in "Gurumahadeo", because it
first checks to see whether Gurumahadeo begins with mahad (does not),
then searches for " mahad", which it does not find because of the
space added to the front.