Finding a text string w/in a Cell
 

I have a column of cells with text strings. I need to match a word
that matchs part of that string and call it out
Using FIND just finds a string and gives the location of the 1st
letter:
E.g
cell D4 = High Pressure Disk
cell C4 is FIND("Disk",D4) equals 15

I need to check if the string "Disk" is in the string and either give a
specific value or the name "Disk"
An IF statement would work as well, except I believe the IF statements
only work with numbers

Anyone have any thoughts?

Thanks


--
ricxl
------------------------------------------------------------------------
ricxl's Profile: http://www.excelforum.com/member.php...o&userid=32412
View this thread: http://www.excelforum.com/showthread...hreadid=521909

Finding a text string w/in a Cell
 

Try something like this.

=IF(ISERROR(SEARCH("Disk",D4,1)0),"Disk Not Found","Found Disk")

Search is not case sensitive so if you want your lookup to be case
sensitive, just use the FIND function. If it does not find the text,
it gives the VALUE error.

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=521909

Finding a text string w/in a Cell
 

Will this do it for you?
=IF(ISNUMBER(SEARCH("disk",D9)),TRUE,FALSE)

SEARCH is not case sensitive, where FIND is case sensitive, and may
cause you to miss data. This is just an FYI to consider.

Finding a text string w/in a Cell
 

To further "call out" that cell if it contains the word Disk, you can
apply the formula to conditional formatting and have it highlight the
cell a color. You are limited to 3 conditions in conditional
formatting though.

Highlight cell D4, Go to Format, Conditional Formatting. Change the
Cell Value is box to Formula is. Use this formula.

=SEARCH("Disk",D4,1)0

Select Format, Patterns and select a color to fill the cell with if the
word Disk appears in the cell. Click OK and OK.


HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=521909

Finding a text string w/in a Cell
 

To go further, I've used the formula below to display a cell that
matches a corresponding location.
I need to run several values as an example below:
Col1 Col 2 Col 3
Col 4
Spool Loc 202 Comp Spool Flange B/H 12 Spool *spool*
Sump Seal Loc 26 C-Sump Seal Support B/H 6 Sump Seal *Sump
Seal*
Spool Loc 187 Comp Spool SRH 12 o/c Spool *spool*
OBP Loc 204 OBP Bolt Hole Top OBP *OBP*
Disk Loc 12 Disk Fwd B/H 6 o/c Disk
*Disk*
Sump Seal Loc 226 C-Sump Seal Support Flan Sump Seal *Sump
Seal*
Aft Shaft Loc 24 Aft Shaft Airhole 6 o/c Aft Shaft
*Aft Shaft*
Aft Shaft Loc 43 Aft Shaft Flange Outer Af etc.
OBP Loc 7 OBP Catenary Fwd Inner Fil
Sump Seal Loc 40 C-Sump Seal Support Airho
Aft Shaft Loc 44 Aft Shaft Fwd Web Above A
Disk Loc 13 Disk Aft B/H 6 o/c
ACP Loc 95 ACP Lower Bore Fillet
ACP Loc 93 ACP Fwd Middle Web

The first column is what I want to extract from the 2nd column

There are several formula's to try this. One that works is the one
suggested above, but I need to still do some manual cut & paste and
change the text within the formula. A thought would be to send the
answer to the same row, but previous column (Col 0?)


Following is another formula that I use to match data, but I would need
to "wild card" the target to get an answer


e.g. I have 4 columns
Col2 is long list items, Col3 is the answer, col4 is a list of items
with wild cards,

the formula (in Col1) looks like this:

=INDEX(col2 ,match(cell col4,range col3))

Problem is wild cards don't work

I guess another method is
=IF(ISNUMBER(SEARCH(disk",Col2)),Col0="DISK",False ), but that doesn't
work either.

Any comments?

Thanks


--
ricxl
------------------------------------------------------------------------
ricxl's Profile: http://www.excelforum.com/member.php...o&userid=32412
View this thread: http://www.excelforum.com/showthread...hreadid=521909

Finding a text string w/in a Cell
 

I'm a little confused as to exactly what you are trying to do. Do you
mean that you want to look up the word in Column A in column B and
return it in column C? There are a couple of ways to do this that
should not require any cutting and pasting.

=IF(ISNUMBER(SEARCH(TRIM(A1),B1,1)),A1,"")

or

=MID(B1,SEARCH(TRIM(A1),B1,1),LEN(TRIM(A1)))

I used the TRIM function because if your data in A:A has any unseen
spaces at the end of the text, without it, the formulas will not return
the desired result.

Maybe I am not understanding completely?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=521909

Finding a text string w/in a Cell
 

Kind of, but the word in "A" is not in the same row as the the phrase in
"B". I have to pull out the simplified "key" words and put them in a
column different from the equation IF I need to rewrite the equation
for each key word

Loc 202 Comp Spool Flange B/H 12 gives "Spool"
Loc 26 C-Sump Seal Support B/H 6 gives "Seal"
Loc 187 Comp Spool SRH 12 o/c gives "Spool"
Loc 204 OBP Bolt Hole Top gives "OBP Bolt"
Loc 12 Disk Fwd B/H 6 o/c gives "Disk"
Loc 226 C-Sump Seal Support Flan gives "Seal"

It's kind of why I was using the match and index functions. THere I
wouldn't have toi change the equation, but I'm kinda resigned that I
will have to

Thanks again for this!


--
ricxl
------------------------------------------------------------------------
ricxl's Profile: http://www.excelforum.com/member.php...o&userid=32412
View this thread: http://www.excelforum.com/showthread...hreadid=521909

Finding a text string w/in a Cell
 

Thanks, for much for the effort in this. I'll try to explain further:
Trying again to explain this

Step 1 - Data in original format: Need to extract the Upper Case
names and input them into a the next column.

column1
acBOBee
eDICKrt
oTOMidk
pHARRYw
dfBOBrr
rtBOBkj
kTOMrrq

Step 2

Column1 1 is the original data. I need to pull a specific string out
of each of the cells:
and put them in to Column2. What is below would be the ideal result,
but what I can now do is only one item, "BOB" for exaple, at a time and
I would need to copy the result for BOB to a 3rd column and then run the
formula again for the next case "DICK" but before that is done, manually
cut & paste BOB to another column then run through the procedure for BOB
DICK HARRY & TOM. Very tedious.

One of the formulas suggested easily extracts the data, but I still
have to cut & paste.

column1 column2
acBOBee BOB rem achieved through
=IF(ISNUMBER(SEARCH("BOB",Col1)),"BOB","")
eDICKrt DICK rem achieved through
=IF(ISNUMBER(SEARCH("DICK",Col1)),"DICK","")
oTOMidk TOM
pHARRYw HARRY
dfBOBrr BOB
rtBOBkj BOB
kTOMrrq TOM

This looks to be the same task that Rookie_User is seeking.

I also thought of matching a value in a 3rd & 4th column, and can do it
with exact matchs, but not when looking at a portion of a string in a
larger string.

Formula would be

=INDEX(col2 ,match(cell col4,range col3))

Col 2 would be the pool of unique data
cell in col4 is the look up
Col4 is match

I'll post this in Rookie_User as well.

Thanks

Ric D


--
ricxl
------------------------------------------------------------------------
ricxl's Profile: http://www.excelforum.com/member.php...o&userid=32412
View this thread: http://www.excelforum.com/showthread...hreadid=521909

Finding a text string w/in a Cell
 

On Thu, 16 Mar 2006 16:30:04 -0600, ricxl
wrote:


Thanks, for much for the effort in this. I'll try to explain further:
Trying again to explain this

Step 1 - Data in original format: Need to extract the Upper Case
names and input them into a the next column.

column1
acBOBee
eDICKrt
oTOMidk
pHARRYw
dfBOBrr
rtBOBkj
kTOMrrq

Step 2

Column1 1 is the original data. I need to pull a specific string out
of each of the cells:
and put them in to Column2. What is below would be the ideal result,
but what I can now do is only one item, "BOB" for exaple, at a time and
I would need to copy the result for BOB to a 3rd column and then run the
formula again for the next case "DICK" but before that is done, manually
cut & paste BOB to another column then run through the procedure for BOB
DICK HARRY & TOM. Very tedious.

One of the formulas suggested easily extracts the data, but I still
have to cut & paste.

column1 column2
acBOBee BOB rem achieved through
=IF(ISNUMBER(SEARCH("BOB",Col1)),"BOB","")
eDICKrt DICK rem achieved through
=IF(ISNUMBER(SEARCH("DICK",Col1)),"DICK","")
oTOMidk TOM
pHARRYw HARRY
dfBOBrr BOB
rtBOBkj BOB
kTOMrrq TOM

This looks to be the same task that Rookie_User is seeking.

I also thought of matching a value in a 3rd & 4th column, and can do it
with exact matchs, but not when looking at a portion of a string in a
larger string.

Formula would be

=INDEX(col2 ,match(cell col4,range col3))

Col 2 would be the pool of unique data
cell in col4 is the look up
Col4 is match

I'll post this in Rookie_User as well.

Thanks

Ric D

I'm not sure exactly what you're trying to do, and that is part of the problem.

To pull out the capitalized words from your column 1, you can use the formula:

=REGEX.MID(A1,"[A-Z]+")

To pull out any of a list of words from your column 1, you could use the
formula:

=REGEX.MID(A1,"(BOB)|(TOM)|(DICK)|(HARRY)")

To make the list more manageable, you could put the names in a range named rng
(one name per cell) and use the formula:

=REGEX.MID(A1,MCONCAT(rng,"|"))

To use the above formulas, you must download and install Longre's free
morefunc.xll add-in from http://xcell05.free.fr

Perhaps if these don't give you what you want, you could elaborate your
requirements.
--ron

Finding a text string w/in a Cell
 

On Thu, 16 Mar 2006 16:30:04 -0600, ricxl
wrote:


Thanks, for much for the effort in this. I'll try to explain further:
Trying again to explain this

Step 1 - Data in original format: Need to extract the Upper Case
names and input them into a the next column.

column1
acBOBee
eDICKrt
oTOMidk
pHARRYw
dfBOBrr
rtBOBkj
kTOMrrq

Step 2

Column1 1 is the original data. I need to pull a specific string out
of each of the cells:
and put them in to Column2. What is below would be the ideal result,
but what I can now do is only one item, "BOB" for exaple, at a time and
I would need to copy the result for BOB to a 3rd column and then run the
formula again for the next case "DICK" but before that is done, manually
cut & paste BOB to another column then run through the procedure for BOB
DICK HARRY & TOM. Very tedious.

One of the formulas suggested easily extracts the data, but I still
have to cut & paste.

column1 column2
acBOBee BOB rem achieved through
=IF(ISNUMBER(SEARCH("BOB",Col1)),"BOB","")
eDICKrt DICK rem achieved through
=IF(ISNUMBER(SEARCH("DICK",Col1)),"DICK","")
oTOMidk TOM
pHARRYw HARRY
dfBOBrr BOB
rtBOBkj BOB
kTOMrrq TOM

This looks to be the same task that Rookie_User is seeking.

I also thought of matching a value in a 3rd & 4th column, and can do it
with exact matchs, but not when looking at a portion of a string in a
larger string.

Formula would be

=INDEX(col2 ,match(cell col4,range col3))

Col 2 would be the pool of unique data
cell in col4 is the look up
Col4 is match

I'll post this in Rookie_User as well.

Thanks

Ric D

Navigating to the excel forum and looking at what you've posted there, it seems
to me that my second solution would work:

=REGEX.MID(B1,MCONCAT(rng,"|"))

In rng you would have your list of lookup strings:

Sump Seal
Spool
OBP
Disk
Aft Shaft



--ron

Finding a text string w/in a Cell
 

Hey, Ron,
That worked really well! The only thing to mention is that it is case
sensitive.
I believe there's another post that is doing the same thing. I'll post
a reply to it as well.
Thanks, again,
Ric


--
ricxl
------------------------------------------------------------------------
ricxl's Profile: http://www.excelforum.com/member.php...o&userid=32412
View this thread: http://www.excelforum.com/showthread...hreadid=521909

Finding a text string w/in a Cell
 

On Fri, 17 Mar 2006 08:05:20 -0600, ricxl
wrote:


Hey, Ron,
That worked really well! The only thing to mention is that it is case
sensitive.
I believe there's another post that is doing the same thing. I'll post
a reply to it as well.
Thanks, again,
Ric

I know it is case sensitive. Is that what you want or not? That's how you
posted all of your examples.

If that is NOT what you want, if you look at the REGEX.MID function, you will
see an option for case sensitivity. Enter FALSE for the argument.


--ron

Finding a text string w/in a Cell
 

I'll check that out. I made the samples uppercase to emphasize the
desired string

Thanks again.


--
ricxl
------------------------------------------------------------------------
ricxl's Profile: http://www.excelforum.com/member.php...o&userid=32412
View this thread: http://www.excelforum.com/showthread...hreadid=521909