Find variable unique text within a string

  • Thread starter Thread starter Roger
  • Start date Start date
R

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 ??
 
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?
 
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 ?
 
Try this...

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

Copied across then down as needed.
 
Back
Top