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

 

[evergreen1]

A1-earth
A2-earthquake
A3-earthworm
A4-pearlearth		erase
A5-goodearth		erase

<tbody>
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]erase[/TD]
[TD="width: 64"]earth[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"]5[/TD]

</tbody>

i have data in A1:A5

I wanted to find out rows with the word "earth" on it. that means the result should be row1 only.


WHEN i applied -search("earth",A1) i got the digits as answers.in col B
when i applied your formula-=IF(COUNT(SEARCH($E$1&" ",A1&" ")),"erase","") i got unexpected results.in col C .kindly see it and comment please.

 

[Aladin Akyurek]

A1-earth
A2-earthquake
A3-earthworm
A4-pearlearth		erase
A5-goodearth		erase

<TBODY>
[TD="align: right"]1
[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]erase
[/TD]
[TD="width: 64"]earth
[/TD]

[TD="align: right"]1
[/TD]

[TD="align: right"]1
[/TD]

[TD="align: right"]6
[/TD]

[TD="align: right"]5
[/TD]

</TBODY>

i have data in A1:A5

I wanted to find out rows with the word "earth" on it. that means the result should be row1 only.


WHEN i applied -search("earth",A1) i got the digits as answers.in col B
when i applied your formula-=IF(COUNT(SEARCH($E$1&" ",A1&" ")),"erase","") i got unexpected results.in col C .kindly see it and comment please.

Try...

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

 

[ace19852]

Hi you could try this in Aladin form with Earth in E1

=IF(LEFT(A1,6)=$E$1&" ","erase",IF(COUNT(SEARCH(" "&$E$1&" ",A1)=1),"erase",IF(COUNT(SEARCH(" "&$E$1,A1)=1),"erase","")))

It will catch the string if the text starts with "Earth "

or if the text is " earth "

or if the text is " earth"

Cheers

ps just occurred to me probably an OR in that formula would shorten it !

 

[Aladin Akyurek]

Hi you could try this in Aladin form with Earth in E1

=IF(LEFT(A1,6)=$E$1&" ","erase",IF(COUNT(SEARCH(" "&$E$1&" ",A1)=1),"erase",IF(COUNT(SEARCH(" "&$E$1,A1)=1),"erase","")))

It will catch the string if the text starts with "Earth "

or if the text is " earth "

or if the text is " earth"

Cheers

ps just occurred to me probably an OR in that formula would shorten it !

Try post #32...

 

[ace19852]

Just checked it's shorter

=IF(OR(LEFT(A1,6)=$E$1&" ",COUNT(SEARCH(" "&$E$1&" ",A1)),COUNT(SEARCH(" "&$E$1,A1))),"erase","")

Cheers

ps I check #32 only works if "-" precedes the word earth as in -earth. I wasn't sure if evergreen really meant that to be the case. In my formula if you have -earth in E1 it will indentify all instances of the word in E1 no matter where it is beginning, middle or end of the text.

 

[Aladin Akyurek]

Just checked it's shorter

=IF(OR(LEFT(A1,6)=$E$1&" ",COUNT(SEARCH(" "&$E$1&" ",A1)),COUNT(SEARCH(" "&$E$1,A1))),"erase","")

Cheers

ps I check #32 only works if "-" precedes the word earth as in -earth. I wasn't sure if evergreen really meant that to be the case. In my formula if you have -earth in E1 it will indentify all instances of the word in E1 no matter where it is beginning, middle or end of the text.

The OP is looking for earth, not for -earth...

 

[ace19852]

The OP is looking for earth, not for -earth...

Exactly what I think.

Evergreen is looking for a word like "event" or "earth" and not "-earth" which is why my formula is as it is.

His request wasn't totally clear.

Btw what does OP mean I guessing it's code for evergreen ?

 

[evergreen1]

Try...

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

Aladin Akyurek,thank you very much

thanks for the patience you showed to solve my issues. this is what i wanted. it is very clear and simple formula,
have nice days ahead.

 

[Aladin Akyurek]

thanks for the patience you showed to solve my issues. this is what i wanted. it is very clear and simple formula,
have nice days ahead.

You are welcome. Thanks for providing feedback.

 

[ace19852]

Well I'm glad you persevered and got the solution you were looking for evergreen it's been a pleasure and I have picked up a few insight along the way. Congrats Aladin I must have misunderstood evergreen need but alls well that ends well