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]

SEARCH looks for an item in a target string. If it locates the item, it reports at which position the item occurs with the target string. Hence those numbers, otherwise we get #VALUE!. COUNT that we used around SEARCH yields a 1 for a number, 0 for #VALUE!. Since a non-zero number means TRUE, we get "erase", otherwise "" with:

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

Try...

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

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.

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




i compared the two formulas and found that the difference is the presence of a dash. what does this character tell excel? I am little bit confused about it. since i don't have a dash before the word "earth". but the formula worked right when it was added!
i would also like to know why does the digits appeared as a result of the first formula in col B? thanks
=======================
aldin said
1)SEARCH looks for an item in a target string. If it locates the item, it reports at which position the item occurs with the target string. Hence those numbers, otherwise we get #VALUE!. COUNT that we used around SEARCH yields a 1 for a number, 0 for #VALUE!. Since a non-zero number means TRUE, we get "erase", otherwise "" with:
2)A1-earth 1 erase earth
A2-earthquake 1
A3-earthworm 1
A4-pearlearth 6 erase
A5-goodearth 5 erase


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.

3)aldin said>SEARCH looks for an item in a target string. If it locates the item, it reports at which position the item occurs with the target string.

here target string is in A1:A5.we are searching for "earth"&""
it is available only in A1 and the position is 1 right?
My doubt is , if it is so, then why the digits in A2:A5,where there is no word we are searching for?
You said> otherwise we get #VALUE!.

even then we got digits for A2:A5.?

 

[Aladin Akyurek]

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




i compared the two formulas and found that the difference is the presence of a dash. what does this character tell excel? I am little bit confused about it. since i don't have a dash before the word "earth". but the formula worked right when it was added!
i would also like to know why does the digits appeared as a result of the first formula in col B? thanks
=======================
aldin said
1)SEARCH looks for an item in a target string. If it locates the item, it reports at which position the item occurs with the target string. Hence those numbers, otherwise we get #VALUE!. COUNT that we used around SEARCH yields a 1 for a number, 0 for #VALUE!. Since a non-zero number means TRUE, we get "erase", otherwise "" with:
2)A1-earth 1 erase earth
A2-earthquake 1
A3-earthworm 1
A4-pearlearth 6 erase
A5-goodearth 5 erase


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.

3)aldin said>SEARCH looks for an item in a target string. If it locates the item, it reports at which position the item occurs with the target string.

here target string is in A1:A5.we are searching for "earth"&""
it is available only in A1 and the position is 1 right?
My doubt is , if it is so, then why the digits in A2:A5,where there is no word we are searching for?
You said> otherwise we get #VALUE!.

even then we got digits for A2:A5.?

See:
https://dl.dropboxusercontent.com/u/65698317/evergreen1%20find%20specific%20text.xlsx

 

[evergreen1]

See:
https://dl.dropboxusercontent.com/u/...ic%20text.xlsx

Hi Aladin, thanks for the response. but nothing new i could conclude. I never questioned your formula's effectiveness in solving my present issue. I was asking to get some additional information as why this- or that happened in that way.

 

[Aladin Akyurek]

Hi Aladin, thanks for the response. but nothing new i could conclude. I never questioned your formula's effectiveness in solving my present issue. I was asking to get some additional information as why this- or that happened in that way.

You can make use of the F9 key on the formula bar to see how a formula bit evaluates.

 

[evergreen1]

You can make use of the F9 key on the formula bar to see how a formula bit evaluates.

thanks for the guidance.

 

[ace19852]

I'm still somewhat confused but it still appears to me that you want to identify the actual word earth and not a word with a dash -earth so reiterating if you want this "Earth " at the beginning of the text " earth " in the middle of the text or "earth" at the end of the text. If I wrong I apologise for this post.

As I previous said the formula below will identify all 5 letter words like event or "Earth " at the beginning of the text " earth " in the middle of the text or "earth" at the end of the text.

If the length of the word is not 5 long change the LEFT(A1,"......6.......") to whatever the length of the work is +1 e.g. if the word is social (6 long) the statement would be LEFT(A1,7).

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

However if you use this formula below it automatically picks up the length of the word so modifying the formula as described above is not necessary. Your chose ?

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


Cheers