Comparing two rows in Excel for a particular word and returning the content found in that cell where the word is in a new row

[Sailadarohit]

I want to compare two rows in Excel for a particular word and return the entire content found in that cell where the word is present in the new rows.
So i am searching with the word nike in the two rows, the formula should search both the rows for the word nike and if found return the entire content found along with the word nike in the new rows else return NULL.
Row A and B are my inputs, output should look like row C and D
Please refer the attachment for more information.

 

[Kerryx]

Book1
	A	B	C	D
1	Act	***	Word_Act	Word_Ass
2	the_nike_tshirt	the_nike_jeans	the_nike_tshirt	the_nike_jeans
3	_the_puma_tshirt	a_puma_jeans	NULL	NULL
4	a_nike_pants	brand_nike_leans	a_nike_pants	brand_nike_leans
Sheet1

Cell Formulas
Range	Formula
C2:D4	C2	=IF(ISERROR(SEARCH("nike",A2,1)),"NULL",A2)

 

[Sailadarohit]

Thanks Kerryx for the response. I have some more requirements here, if the rows (A,B) has other data as well along with the search word separated by commas, can i get only that search word return in my result(C,D). Please see the attachment.

 

[Kerryx]

Just change formula to

=IF(ISERROR(SEARCH("nike",A2,1)),"NULL",LEFT(A2,(FIND(",",A2)-1)))

 

[Sailadarohit]

Just change formula to

=IF(ISERROR(SEARCH("nike",A2,1)),"NULL",LEFT(A2,(FIND(",",A2)-1)))

the word can be anywhere, not always it would be on the second position.. it can change.

 

[Sailadarohit]

Hi Kerryx , Could you pls help me with the question which i have asked above

 

[Peter_SSs]

Any SEARCH("nike",... type formula would find a match in the cell below, which I assume would not be what is wanted?

If I am correct, can we assume that the word being searched for is always preceded and followed by an underscore? That is, the word would never be the first or last word in a section?

In your 365/2021 versions, do you have the TEXTSPLIT function? That is, if you begin to type =TEXT into an empty cell, do you get this option appear?

Could the word being searched for ever appear more than once in a cell? If so, can you give an example and include the expected result?

 

[Sailadarohit]

The word is always preceded by an underscore but not always we can assume that is followed by an underscore.
For work purposed i have to use Excel 2016 so no TEXTSPLIT

Thanks in advance

 

[Peter_SSs]

The word is always preceded by an underscore but not always we can assume that is followed by an underscore.

So, could we have some sample data and expected results that show the variety that can occur? Using XL2BB will likely get you more helpers and faster answers as we can then copy the data to test with. Many potential helpers will just bypass your thread if they have to manually type out sample data.

What about my other question?

Could the word being searched for ever appear more than once in a cell? If so, can you give an example and include the expected result?

 

[Sailadarohit]

Hi Peter, I have tried doing this for adding XL2BB but microsoft is not allowing me to use that.. at one point i did turn off windows defender then also it did not work but i dont think its good for my privacy & security to turn off the defender.

I am stuck with the above problem and I have to deliver it on priority..could you pls help
For the other part of question which i missed.. Could the word being searched for ever appear more than once in a cell--No it won't appear

Please find the snippet attached.
I want to compare two rows in Excel for a particular word and return only the content found in that cell where the word is present in the new rows.
So i am searching with the word nike in the two rows, the formula should search both the rows for the word nike and if found return the entire content found along with the word nike in the new rows else return NULL

if possible pls provide solutions using TEXTSPLIT and a solution for Excel 2016 as there is no TEXTSPLIT option