Finding a text string w/in a Cell

[ricxl]

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

[SteveG]

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]

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

[Dave O]

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.

[ricxl]

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:
Col 1, Col2, Col3, Col4
Spool, Loc202CompSpoolFlange, Spool, *spool*
Sump Seal, Loc26C-Sump Seal Support, Sump Seal, *Sump Seal*
Spool, Loc187CompSpoolSRH, Spool, *spool*
OBP, Loc204OBPBoltHoleTop, OBP, *OBP*
Disk, Loc12DiskfwdB/H, Disk, *Disk*
Sump Seal, Loc226Sump SealFlan,Sump Seal, *Sump Seal*
Aft Shaft , Loc24Aft ShaftAirhole6, Aft Shaft, *Aft Shaft*
Aft Shaft, Loc43Aft ShaftFlangeOuter, etc.
OBP, LocOBPCatenaryFwd Inner,,
Sump Seal, Loc40C-Sump SealSupport,
Aft Shaft , Loc44Aft ShaftFwdWebAbove,

This may not look right! I'll delimit the columns with commas

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

[SteveG]

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

[ricxl]

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]

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 "****" but before that is done, manually cut & paste BOB to another column then run through the procedure for BOB **** 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 **** rem achieved through =IF(ISNUMBER(SEARCH("****",Col1)),"****","")
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

[Ron Rosenfeld]

On Thu, 16 Mar 2006 16:30:04 -0600, ricxl
<[email protected]> 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 "****" but before that is done, manually
>cut & paste BOB to another column then run through the procedure for BOB
>**** 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 **** rem achieved through
>=IF(ISNUMBER(SEARCH("****",Col1)),"****","")
>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)|(****)|(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

[Ron Rosenfeld]

On Thu, 16 Mar 2006 16:30:04 -0600, ricxl
<[email protected]> 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 "****" but before that is done, manually
>cut & paste BOB to another column then run through the procedure for BOB
>**** 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 **** rem achieved through
>=IF(ISNUMBER(SEARCH("****",Col1)),"****","")
>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

[ricxl]

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

[Ron Rosenfeld]

On Fri, 17 Mar 2006 08:05:20 -0600, ricxl
<[email protected]> 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

[ricxl]

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

Thanks again.