Hello again,

I've gotten the formula for finding occurrences of a word in one
column based on whether it's in another column; now what I need is to
find out whether a word in column A (for instance) appears in a string
of text in column B. As in, a SKU in A, and a description in B, with
the descriptions having several SKUs in each one. I need to be able
to delete from a sheet any description that doesn't have the SKUs I'm
looking for, and it would be really nice to color the corresponding
cells the same, but even having the row number in the third row would
be nice.

Say I've got SKUs on widely separated rows, all pointing to one cell
in B, and then in C, the row that has the SKU in A would have the row
number of the cell in B that the string is in. Make sense?

Thanks!
--
Teresa Robinson
Staff Programmer Analyst
Anteon Corporation
trobinson at anteon dot com

Teresa, your explaination has me a bit confused but I'm gonna take a
shot at this anyhow. I am assuming that you want to be able to use a
particular cell on your worksheet to enter some text into and then be
able to see which cells in your sheet contain an instance of the search
text. If this is true then try this approach...

Assuming that the cell you are using to enter your search criteria in
is named Criteria. And that you use a formula similar to this one below
as a conditional format in every cell you wish to search.

=LEN(A3)-LEN(SUBSTITUTE(SUBSTITUTE(A3,PROPER(Criteria),""), Criteria,""))0
If you do not know how to do:

1 Name a cell OR
2 Apply conditional formats

then just tell me what you don't understand and I'll get back to you.
If you use this method, you can have all the cells that contain your
search string change colors instantly so that it is easier for you to
sort them out. Hope this is in the ballpark...Mark

You won't *believe* what " said on
13 Oct 2005 19:37:46 -0700, in microsoft.public.excel.newusers...:

Teresa, your explaination has me a bit confused but I'm gonna take a
shot at this anyhow. I am assuming that you want to be able to use a
particular cell on your worksheet to enter some text into and then be
able to see which cells in your sheet contain an instance of the search
text. If this is true then try this approach...

Assuming that the cell you are using to enter your search criteria in
is named Criteria. And that you use a formula similar to this one below
as a conditional format in every cell you wish to search.

=LEN(A3)-LEN(SUBSTITUTE(SUBSTITUTE(A3,PROPER(Criteria),""), Criteria,""))0
If you do not know how to do:

1 Name a cell OR
2 Apply conditional formats

then just tell me what you don't understand and I'll get back to you.
If you use this method, you can have all the cells that contain your
search string change colors instantly so that it is easier for you to
sort them out. Hope this is in the ballpark...Mark

Thanks, Mark, I'll give this a try.

Thing is, everything I've seen on finding "text" in column B from
"text" in column A, assumes that "text" is a single word in both
places. As in, cell A1 has "smith", so find any cell in B that also
has "smith". This works great, but what if the cell in B has "mr
smith is outside" and you want to be able to find that cell? The
routines I've found don't work for this.

--
Teresa Robinson
Staff Programmer Analyst
Anteon Corporation
trobinson at anteon dot com

Teresa:

The formula that I gave you before to use in the conditional
formatting dialogue works for finding a string of text either solo or
within another string. I use it myself frequently. It will
find"smith" as "Smith","smith" or "Mr Smith" OR "Dave smith ate a
porkchop". Lemme know how things are going... Mark
'

You won't *believe* what " said on
14 Oct 2005 13:06:58 -0700, in microsoft.public.excel.newusers...:

Teresa:

The formula that I gave you before to use in the conditional
formatting dialogue works for finding a string of text either solo or
within another string. I use it myself frequently. It will
find"smith" as "Smith","smith" or "Mr Smith" OR "Dave smith ate a
porkchop". Lemme know how things are going... Mark
'

Will do. Had to spend the weekend putting in a new hard drive and
re-installing everything, and getting all my files off of my web
hosts. I haven't been able to put this to use yet, but I hope to this
evening.

Thanks!
--
Teresa Robinson
Staff Programmer Analyst
Anteon Corporation
trobinson at anteon dot com

On 14 Oct 2005 13:06:58 -0700, "
wrote:

Teresa:

The formula that I gave you before to use in the conditional
formatting dialogue works for finding a string of text either solo or
within another string. I use it myself frequently. It will
find"smith" as "Smith","smith" or "Mr Smith" OR "Dave smith ate a
porkchop". Lemme know how things are going... Mark
'

Well, I finally got around to where I could use this, and I guess I
don't get it like I thought I did. I figured out naming a cell, and I
named a "range", since I have 360 cells in A that I'm looking at. I
have 685 values in A on another sheet, and all 360 from sheet 1 are in
this list. The range on sheet 1 is named Criteria, and I chose the
range on sheet 2 and went into Format - Conditional, chose Formula,
made the format a yellow hilite, and pasted in the formula from the
previous post. But none of the cells in sheet 2 lit up.

It worked great if I put the formula in one cell, one of the cells in
Criteria; it sure lit itself up fast. ;-)

The problem here is that I lost the macro I had for just simple text
finding, during the computer crash and all. No clue how that
happened. So I figured I'd put your formula in there, see what I
could do with it. Found out I couldn't do anything with it.

I'm going to do a bit more research on this, it's not like it's a big
deal; I've totally re-worked the inventory so that I don't *have* to
do a search and replace anymore. But it sure would be helpful to
still be able to do this sort of thing.

Thanks for your help!
--
Teresa Robinson

Teresa:
After reading this new post, I am no longer sure that I understood
your initial question. If you don't mind, E-mail me a copy of your
sheets and I'll investigate it .

Checkout the SEARCH() function. In VBA it would be InStr()
--
Gary''s Student


"Teresa Robinson" wrote:

Hello again,

I've gotten the formula for finding occurrences of a word in one
column based on whether it's in another column; now what I need is to
find out whether a word in column A (for instance) appears in a string
of text in column B. As in, a SKU in A, and a description in B, with
the descriptions having several SKUs in each one. I need to be able
to delete from a sheet any description that doesn't have the SKUs I'm
looking for, and it would be really nice to color the corresponding
cells the same, but even having the row number in the third row would
be nice.

Say I've got SKUs on widely separated rows, all pointing to one cell
in B, and then in C, the row that has the SKU in A would have the row
number of the cell in B that the string is in. Make sense?

Thanks!
--
Teresa Robinson
Staff Programmer Analyst
Anteon Corporation
trobinson at anteon dot com

You won't *believe* what Gary''s Student
said on Thu, 13 Oct 2005
21:21:43 -0700, in microsoft.public.excel.newusers...:

Checkout the SEARCH() function. In VBA it would be InStr()

Thanks, Gary; I'll use this in later code, when I need to be doing
price changes. I'm just not looking forward to that at all.

--
Teresa Robinson
Staff Programmer Analyst
Anteon Corporation
trobinson at anteon dot com