Find variable unique text within a string

[Roger]

Hi.
I have a large spreadsheet where I have listed System number vertically and
horisontally in a Matrix.
What I want to do is to identify / mark which record in the vertical listing
of these system numbers that are listed in column C (Links)
Starting in Column D1 I have used the following function and copied it
across the matrix : =IF(ISNUMBER(SEARCH(D$1,$C2)),"Yes","")

The spreadsheet looks like this:
A B C D
E F
"System" "Description" "Links" 45761 71203
72203 .......etc etc
71203 Text1 22377;45761 Yes
48958 Text2 871203 Yes
65321 Text3 92458;87203
Yes
etc
etc

The first "Yes" is OK because the links columns actually has the numbers
"45761" in the links column.
However the next 2 "Yes" are erroneous because the function return a true
value due to the fact that the links listed for these records contain these
last 5 digits as part of the links numbers listed there and so they return a
"Yes" in these columns.

I want to eliminate the last two "Yes" through a revised function but I
can't seem to find one that that will do this for me.
I have tried including Exact and various others in the above function
formula but that doesn't work either.
Anyone got a solution for this ??

 

[T. Valko]

I can see how the 2nd Yes is matching 71203 in 871203 but I can't see where
the 3rd Yes is matching anything.

BTW, when you paste multiple columns of data into a post the alignment
usually gets hosed and it ends up as scrambled mess.

Is the 3rd Yes related to 72203? What's it supposed to be matching?

 

[Roger]

Oops, sorry about the typo. Yes the 3'rd Yes is related to 72203 and hence
the intention on my part was that the link in column C shoud be 872203 and
not 87203 as you correctly pointed out ;o/
BTW, I did not copy this from a spreadsheet but entered it in manually and
the alignment looked fine when I posted it.
I've corrected the typo and re-aligned the columns again so hopefully it
doesn't look as messy.

Anyway, the question remains: How can I remove the two last Yes'es ?

 

[T. Valko]

Try this...

=IF(ISNUMBER(SEARCH(";"&D$1&";",";"&$C2&";")),"Yes","")

Copied across then down as needed.

 

[Roger]

Hi Thanks man, this works like a clock
Great stuff

 

[T. Valko]

You're welcome. Thanks for the feedback!