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

 

[Aladin Akyurek]

Given:
suppose i have the following in a range A1:A999.
[TABLE="width: 432"]
<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"]
<TBODY>[TR]
[TD="width: 432"]A3:[Date "1857.10.23"]
[/TD]
[/TR]
</TBODY>[/TABLE]


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

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

[TABLE="width: 432"]
<TBODY>[TR]
[TD="width: 432"]A6:[Event "Rubinstein mem"]
[TABLE="width: 432"]
<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.

B1, copied down:

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

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

Formulas cannot erase/delate cells they refer to. Apply Autofilter and delete every record marked with erase in B.

 

[evergreen1]

Aladin Akyurek,

[=IF(COUNT(SEARCH($E$1&"|",A1&"|")),"erase","")/QUOTE]
i have pasted the formula in B1 and copied down .

it gives me the result "erase" in all the columns in B irrespective of the contents in col A

 

[Aladin Akyurek]

Aladin Akyurek,

[=IF(COUNT(SEARCH($E$1&"|",A1&"|")),"erase","")/QUOTE]
i have pasted the formula in B1 and copied down .

it gives me the result "erase" in all the columns in B irrespective of the contents in col A

B1, copied down:

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

See:
https://dl.dropboxusercontent.com/u/65698317/evergreen1.xlsx

 

[evergreen1]

Aladin Akyurek,




B1, copied down:

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

See:
https://dl.dropboxusercontent.com/u/65698317/evergreen1.xlsx

t

Thanks a lot Aladin Akyurek,

you didn"t tell me to put the word event in E1

.now i got it and it works. thanks. have a nice day.
btw, i would like to know,why the formula doesn't pick the rows with eventdate?

what differentiates event and eventdate?

 

[Aladin Akyurek]

t

.now i got it and it works. thanks. have a nice day.

You are welcome.

btw, i would like to know,why the formula doesn't pick the rows with eventdate?

The formula is set up for looking event followed by space, i.e., $E$1&" ". the eventdate entry has no space between event and date, so it will be skipped.

 

[evergreen1]

You are welcome.



The formula is set up for looking event followed by space, i.e., $E$1&" ". the eventdate entry has no space between event and date, so it will be skipped.

thanks for the quick reply. i got it. but the same thing i would like to ammend like this
if the word "event" doesn't has a space then how would you frame the formula?(for learning purpose)

 

[Aladin Akyurek]

thanks for the quick reply. i got it. but the same thing i would like to ammend like this
if the word "event" doesn't has a space then how would you frame the formula?(for learning purpose)

SEARCH gets anything with event in it:

SEARCH("event",A1)

In order not to get anything with event in it, the following formula enriches the event entry:

SEARCH("event ",A1&" ")

Adding a space after the event entry requires adding space also after the target string in A1 for event with space would match just event in A1, an unintended result.

 

[evergreen1]

SEARCH gets anything with event in it:

SEARCH("event",A1)

In order not to get anything with event in it, the following formula enriches the event entry:

SEARCH("event ",A1&" ")

Adding a space after the event entry requires adding space also after the target string in A1 for event with space would match just event in A1, an unintended result.

SEARCH("event",A1)

when applied on B1 in post No21 i got the following
B1-5
B2-4
B3-#value
B4-5
B5-5
why this digits as results?

 

[ace19852]

Assuming we arrived at the same place with Aladin my formula which is a little longer than Aladin would do the same job but not need to have Event in E1. As you know there are usually more than 1 way to do the same thing in excel. However I still think there is a further clarification you have not yet declared that will change the result.

=IFERROR(IF(SEARCH("event ",A1&" "),"Erase",""),"")

Cheers

 

[Aladin Akyurek]

when applied on B1 in post No21 i got the following
B1-5
B2-4
B3-#value
B4-5
B5-5
why this digits as results?

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","")