search texts in one cell and return in specific texts

[freebie]

Hello,
I have two questions that I really appreciate if you could help me on it.

I have a list of data. For example:


<table style="width: 359px; border: 1px solid black; border-collapse: collapse; height: 657px;" align=""><tbody><tr><td style="border: 1px solid black; text-align: left; vertical-align: middle; letter-spacing: 0pt; word-spacing: 0pt;" align="center">A



</td><td style="border: 1px solid black;" rowspan="1" align="center">B



</td><td style="border: 1px solid black;" align="center">C



</td><td style="border: 1px solid black;" rowspan="1" align="center">D</td></tr><tr><td style="border: 1px solid black;" align="center">1



</td><td style="border: 1px solid black;" rowspan="1" align="center">Hello World, this is a demo



</td><td style="border: 1px solid black;" align="center">demo



</td><td style="border: 1px solid black;" rowspan="1" align="center">demo</td></tr><tr><td style="border: 1px solid black;" align="center">2



</td><td style="border: 1px solid black;" rowspan="1" align="center">Hello 123, demo only



</td><td style="border: 1px solid black;" align="center">demo



</td><td style="border: 1px solid black;" rowspan="1" align="center">demo</td></tr><tr><td style="border: 1px solid black;" align="center">3



</td><td style="border: 1px solid black;" rowspan="1" align="center">Hello 323, demo123 only, please ignore



</td><td style="border: 1px solid black;" align="center">please



</td><td style="border: 1px solid black;" rowspan="1" align="center">please</td></tr><tr><td style="border: 1px solid black;" align="center">4



</td><td style="border: 1px solid black;" rowspan="1" align="center">Hello 234, testing1234, hi hello



</td><td style="border: 1px solid black;" align="center">ignore



</td><td style="border: 1px solid black;" rowspan="1" align="center">ignore</td></tr><tr><td style="border: 1px solid black;" align="center">5



</td><td style="border: 1px solid black;" rowspan="1" align="center">Hello 422, please ignore me!



</td><td style="border: 1px solid black;" align="center">demo5



</td><td style="border: 1px solid black;" rowspan="1" align="center">demo5</td></tr><tr><td style="border: 1px solid black;" align="center">6



</td><td style="border: 1px solid black;" rowspan="1" align="center">Hello 223, demo5 in progress



</td><td style="border: 1px solid black;" align="center">223



</td><td style="border: 1px solid black;" rowspan="1" align="center">223</td></tr><tr><td style="border: 1px solid black;" align="center">7



</td><td style="border: 1px solid black;" rowspan="1" align="center">Hello 324, demo6 is not completed!



</td><td style="border: 1px solid black;" align="center">is not



</td><td style="border: 1px solid black;" rowspan="1" align="center">is not</td></tr></tbody></table>


What is the excel formula to search B-column and return the result back to D-column. (C-column contains the specific text i would like to search). Also, is it possible to create list of C-column in another tab and it will do same thing as function as I mention above.

Thanks for your help.
F

 

[freebie]

Hello,
I have two questions that I really appreciate if you could help me on it.

I have a list of data. For example:

<table style="width: 595px; border: 1px solid black; border-collapse: collapse; height: 517px;" align=""><tbody><tr><td style="border: 1px solid black; text-align: left; vertical-align: middle; letter-spacing: 0pt; word-spacing: 0pt;" width="5%">A



</td><td rowspan="1" style="border: 1px solid black;" width="39%">B

</td><td style="border: 1px solid black;" width="11%">C
</td><td style="border: 1px solid black;" width="11%">D
</td></tr><tr><td style="border: 1px solid black;">1



</td><td style="border: 1px solid black;" rowspan="1">Hello World, this is a demo



</td><td style="border: 1px solid black;">demo
</td><td style="border: 1px solid black;">demo
</td></tr><tr><td style="border: 1px solid black;">2



</td><td style="border: 1px solid black;" rowspan="1">Hello 123, demo only



</td><td style="border: 1px solid black;">demo
</td><td style="border: 1px solid black;">demo
</td></tr><tr><td style="border: 1px solid black;">3



</td><td style="border: 1px solid black;" rowspan="1">Hello 323, demo123 only, please ignore



</td><td style="border: 1px solid black;">please
</td><td style="border: 1px solid black;">please
</td></tr><tr><td style="border: 1px solid black;">4



</td><td style="border: 1px solid black;" rowspan="1">Hello 234, testing1234, hi hello



</td><td style="border: 1px solid black;">ignore
</td><td style="border: 1px solid black;">ignore
</td></tr><tr><td style="border: 1px solid black;">5



</td><td style="border: 1px solid black;" rowspan="1">Hello 422, please ignore me!
</td><td style="border: 1px solid black;">emo5
</td><td style="border: 1px solid black;">demo5
</td></tr><tr><td style="border: 1px solid black;">6



</td><td style="border: 1px solid black;" rowspan="1">Hello 223, demo5 in progress



</td><td style="border: 1px solid black;">223
</td><td style="border: 1px solid black;">223
</td></tr><tr><td style="border: 1px solid black;">7



</td><td style="border: 1px solid black;" rowspan="1">Hello 324, demo6 is not completed!



</td><td style="border: 1px solid black;">is not
</td><td style="border: 1px solid black;">is not
</td></tr></tbody></table>




1. What is the excel formula to search B-column and return the result back to D-column. (C-column contains the specific text i would like to search).

2. is it possible to create list of specific text that I would like to search with in another tab and it will do same thing as function as I mention above. [Tab1-data, Tab2-text]--Return search data back to D-column under Tab1

Thanks for your help.
F

 

[energman58]

Sorry can you be a bit clearer what you want - do you want to look for teh text that is in C2 in B2 and return the text in D2 if it is present or are you trying to look in the whole of column B for the text in C2 and if a match is found return the whole word to D2 regardless of which row it is in or something else.

In either case what happens if your text is not found and secondly what happens if there is a space in the searched text for example:

Match text "dog"

Searched text "old ogre" - match or no match?

Also, what do you want to do if it appears more than once - for example:

Match text "tar"

Text searched "starboard targets tar" - what do you want returning?

Also if you want to search the whole column do you want the first match returning - for example

Search term "tar"

Star
Tar
Tartar

Returns what?

 

[Joe4]

In your example, it looks like rows 4 and 5 do not have a match, so I don't think you should have those values in column D.

Here is a formula you can use for column D that I think will do that you want. If a match is not found, it will return "NOT FOUND":

=IF(ISERR(FIND(C1,B1)>0),"NOT FOUND",C1)

(for row 1, can copy down for all other rows)

 

[freebie]

In your example, it looks like rows 4 and 5 do not have a match, so I don't think you should have those values in column D.

Here is a formula you can use for column D that I think will do that you want. If a match is not found, it will return "NOT FOUND":

=IF(ISERR(FIND(C1,B1)>0),"NOT FOUND",C1)

(for row 1, can copy down for all other rows)

hi Joe4,

Thanks for your response. I do not know how did this thread come up, lol, I think I might have duplicated thread...I will stick with my original thread

F

 

[freebie]

Sorry can you be a bit clearer what you want - do you want to look for teh text that is in C2 in B2 and return the text in D2 if it is present or are you trying to look in the whole of column B for the text in C2 and if a match is found return the whole word to D2 regardless of which row it is in or something else.

In either case what happens if your text is not found and secondly what happens if there is a space in the searched text for example:

Match text "dog"

Searched text "old ogre" - match or no match?

Also, what do you want to do if it appears more than once - for example:

Match text "tar"

Text searched "starboard targets tar" - what do you want returning?

Also if you want to search the whole column do you want the first match returning - for example

Search term "tar"

Star
Tar
Tartar

Returns what?

Hi energman58,
sorry for any confusion.

Basically, I have "Hello world, this is a demo" from B1 right? and I am wondering if there is a formula or function that could match or compare word "demo" in that sentence. If word "demo" found/detected in B1 cell then I would like "demo" return or print under cell D1. Basically, I do not want to eliminate whole sentence and I would like to keep certain keyword "demo" under D1 for storage purpose. But, the keyword under A-column could be in different position though.

Match text "tar"

Text searched "starboard targets tar" - what do you want returning?

for above case, i would like "tar" return and print in different column.

or


Match text "dog"

Text searched "hello target dog starboard targets tar" - what do you want returning?

i would like "dog" return and print in different column.

Thanks and best regards,
F

 

[Joe4]

I will merge the two together. Note that if you click Submit twice, it will actually submit it twice (even if it appears to be "hanging").

 

[freebie]

I will merge the two together. Note that if you click Submit twice, it will actually submit it twice (even if it appears to be "hanging").

Thanks for your help.