Find variable unique text within a string
 

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 ??
--
The Oilman

Find variable unique text within a string
 

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?

--
Biff
Microsoft Excel MVP


"Roger" wrote in message
...
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 ??
--
The Oilman

Find variable unique text within a string
 

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 ?

--
The Oilman


"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Roger" wrote in message
...
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
71203 Text1 22377;45761 Yes
48958 Text2 871203 Yes
65321 Text3 92458;872203 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 ??
--
The Oilman


.

Find variable unique text within a string
 

Try this...

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

Copied across then down as needed.

--
Biff
Microsoft Excel MVP


"Roger" wrote in message
...
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 ?

--
The Oilman


"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Roger" wrote in message
...
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
71203 Text1 22377;45761 Yes
48958 Text2 871203
Yes
65321 Text3 92458;872203
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 ??
--
The Oilman


.

Find variable unique text within a string
 

Hi Thanks man, this works like a clock :-)
Great stuff
--
The Oilman


"T. Valko" wrote:

Try this...

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

Copied across then down as needed.

--
Biff
Microsoft Excel MVP


"Roger" wrote in message
...
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 ?

--
The Oilman


"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Roger" wrote in message
...
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
71203 Text1 22377;45761 Yes
48958 Text2 871203
Yes
65321 Text3 92458;872203
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 ??
--
The Oilman


.



.

Find variable unique text within a string
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Roger" wrote in message
...
Hi Thanks man, this works like a clock :-)
Great stuff
--
The Oilman


"T. Valko" wrote:

Try this...

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

Copied across then down as needed.

--
Biff
Microsoft Excel MVP


"Roger" wrote in message
...
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 ?

--
The Oilman


"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Roger" wrote in message
...
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
71203 Text1 22377;45761 Yes
48958 Text2 871203
Yes
65321 Text3 92458;872203
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 ??
--
The Oilman


.



.