Finding text within text

  • Thread starter Thread starter Teresa Robinson
  • Start date Start date
T

Teresa Robinson

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, 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
 
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
Click to expand...

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.
 
You won't *believe* what Gary''s Student
Checkout the SEARCH() function. In VBA it would be InStr()
Click to expand...

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:

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
'
 
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
'
Click to expand...

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:

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
'
Click to expand...

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:
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 (e-mail address removed).
 
Back
Top