Find specific text

[mattyman]

Hello All

I need to pull a specific word from a string of text in a cell and have that word shown in an adjacant cell.
For example A1 will contain the text "Smith Sun Alliance Pension Fund"
I need B2 to show "Pension". I cannot use any filtering or text to columns as the word Pension can be anywhere within the text in A1 and I have thousands of entries. So I need a function.

Help would be as always greatly appreciated.

Thanks

Matt

 

[just_jon]

Sorry about screwing it up earlier!

 

[mattyman]

You didn't screw it up.

Thanks very much for your help

Matt

 

[netizenn]

Thanks! I was looking for the same thing...did the trick...
I posted a similar challenge here:
http://www.mrexcel.com/forum/excel-...ontaining-specific-part-text.html#post3349210

Thanks again!

 

[Aladin Akyurek]

Thanks! I was looking for the same thing...did the trick...
I posted a similar challenge here:
http://www.mrexcel.com/forum/excel-...ontaining-specific-part-text.html#post3349210

Thanks again!

See your original thread for a lookup version...

The array formula just-jon, a great friend, has proposed...

{=INDEX(D$1:D$3,MATCH(1,(--ISNUMBER(SEARCH(D$1:D$3,A1))),0),0)}

can be re-written as:

=LOOKUP(9.999999999999999E+307,SEARCH(D$1:D$3,A1),D$1:D$3)

which needs only enter.

 

[evergreen1]

The way you describe your query boils down to testing a string whether it contains a substring of interest...

=IF(COUNT(SEARCH("Pension",A1)),"Pension","")

this will bring all the rows with the word pension and pensionfund.but

if i want to find only the text pension AND NOT the text pensionfund

What to do?

 

[Aladin Akyurek]

this will bring all the rows with the word pension and pensionfund.but
What to do?

Let's try...

=IF(COUNT(SEARCH("|"&"Pension"&"|","|"&A1&"|")),"Pension","")

If you place the Pension entry in E1...

=IF(COUNT(SEARCH("|"&$E$1&"|","|"&A1&"|")),$E$1,"")

 

[evergreen1]

Let's try...

=IF(COUNT(SEARCH("|"&"Pension"&"|","|"&A1&"|")),"Pension","")

If you place the Pension entry in E1...

=IF(COUNT(SEARCH("|"&$E$1&"|","|"&A1&"|")),$E$1,"")

i have entries in column A1:A999. some rows contain the text Event and some Eventdate. I want to find out the rows with the word event rows with the entry eventdate will remain , that should not come in the search)and to delete them.
on D1 i have entered your formula replacing the word Event for the word pension, but nothing happened. help pls

 

[Aladin Akyurek]

i have entries in column A1:A999. some rows contain the text Event and some Eventdate. I want to find out the rows with the word event rows with the entry eventdate will remain , that should not come in the search)and to delete them.
on D1 i have entered your formula replacing the word Event for the word pension, but nothing happened. help pls

Given:

A1: event
A2: eventdate
A3: event

What do you want to happen?

 

[evergreen1]

Given:
suppose i have the following in a range A1:A999.
[TABLE="width: 432"]
<colgroup><col width="432"></colgroup><tbody>[TR]
[TD="width: 432"]A1:[Event "1st American Chess Congress"]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 432"]
<tbody>[TR]
[TD="width: 432"]A2.EventDate "1857.??.??"]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 432"]
<colgroup><col width="432"></colgroup><tbody>[TR]
[TD="width: 432"]A3:[Date "1857.10.23"]
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 432"]
<colgroup><col width="432"></colgroup><tbody>[TR]
[TD="width: 432"]A4;[Event "Lugano Ol"]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 432"]
<colgroup><col width="432"></colgroup><tbody>[TR]
[TD="width: 432"]A5:[EventDate "?"]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 432"]
<colgroup><col width="432"></colgroup><tbody>[TR]
[TD="width: 432"]A6:[Event "Rubinstein mem"]
[TABLE="width: 432"]
<colgroup><col width="432"></colgroup><tbody>[TR]
[TD="width: 432"]A7:[EventDate "?"]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
NOW I want that excel on column B show "erase me" against rows A1.A4,A6.

 

[evergreen1]

since the purpose is to delete the rows with "erase me" is it possible to find and delete in one go