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.

 

[Peter_SSs]

I have tried doing this for adding XL2BB but microsoft is not allowing me to use that..

Try putting the Add-In into a 'Trusted Location'

Could the word being searched for ever appear more than once in a cell--No it won't appear

Thanks for that clarification.


What about this though?

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?

From what I can see in your latest image, every occurrence of "nike" (& "puma") is preceded and followed by an underscore.

 

[Sailadarohit]

From what I can see in your latest image, every occurrence of "nike" (& "puma") is preceded and followed by an underscore.-- Yes we can assume this and formulate the solution accordingly.
Pls ignore my previous statement related to this.

Thanks in advance

 

[Peter_SSs]

Try this then

Sailadarohit_1.xlsm
	A	B	C	D
1	Actual	Assumed	WordPresent Actual	WordPresent Assumed
2	Dress, brand_nike_costly_new, Store	Jeans, brand_nike_air_cheap_Used, Online	brand_nike_costly_new	brand_nike_air_cheap_Used
3	Online, brand_puma_shirt, Paid	Store, brand_puma_jeans, Return	NULL	NULL
4	Offline, brand_nike_jeans, Card	Shirt, brand_nike_tshirts	brand_nike_jeans	brand_nike_tshirts
5	Dress, brand_moniker_costly_new, Store	Jeans, brand_moniker_costly_new	NULL	NULL
Sheet1

Cell Formulas
Range	Formula
C2:D5	C2	=IFERROR(REPLACE(LEFT(A2,FIND(",",A2&",",SEARCH("_nike_",A2))-1),1,FIND(",",A2)+1,""),"NULL")

 

[Sailadarohit]

Thank you so much.. there is a last case which needs to be addressed, in some cases a single string can be present without any commas which can have the key word, the current formula does not address that like for example. Please help

Actual Assumed Word Present Actual Word Present Assume
black_nike_pro black_nike_beta black_nike_pro black_nike_beta

 

[Sailadarohit]

Thank you so much.. there is a last case which needs to be addressed, in some cases a single string can be present without any commas which can have the key word, the current formula does not address that like for example. Please help

Actual Assumed Word Present Actual Word Present Assume
black_nike_pro black_nike_beta black_nike_pro black_nike_beta

Please find the attached image where the nike word is not proceeded by underscore also the case where a single string can be present without any commas which can have the search word, our formula must accomodate these as well

 

[Peter_SSs]

nike word is not proceeded by underscore

You did say to ignore that.

is preceded and followed by an underscore.-- Yes we can assume this and formulate the solution accordingly.
Pls ignore my previous statement related to this.

also the case where a single string can be present without any commas which can have the search word, our formula must accomodate these as well

Then examples like that should have been provided to start with.

You can try this but if there is nothing to mark the end of the searched word, you could get false returns - see row 9

Sailadarohit_1.xlsm
	A	B	C	D
1	Actual	Assumed	WordPresent Actual	WordPresent Assumed
2	Dress, brand_nike_costly_new, Store	Jeans, brand_nike_air_cheap_Used, Online	brand_nike_costly_new	brand_nike_air_cheap_Used
3	Online, brand_puma_shirt, Paid	Store, brand_puma_jeans, Return	NULL	NULL
4	Offline, brand_nike_jeans, Card	Shirt, brand_nike_tshirts	brand_nike_jeans	brand_nike_tshirts
5	Dress, brand_moniker_costly_new, Store	Jeans, brand_moniker_costly_new	NULL	NULL
6	black_nike_pro	black_nike_beta	black_nike_pro	black_nike_beta
7	online,black_nike_pro	black_nike_air	black_nike_pro	black_nike_air
8	offline,red_nike,pants	shop,red_nike_air,shirts	red_nike	red_nike_air
9	offline,red_nikersly,pants	shop,red_nikersly_air,shirts	red_nikersly	red_nikersly_air
Sheet1

Cell Formulas
Range	Formula
C2:D9	C2	=IFERROR(TRIM(REPLACE(LEFT(A2,FIND(",",A2&",",SEARCH("_nike",A2))-1),1,IFERROR(FIND(",",LEFT(A2,SEARCH("_nike",A2))),0),"")),"NULL")

 

[Sailadarohit]

Thank you so much peter
And sorry for not being able to provide all use cases once because unfortunately they keep on adding
For case 9 i think i can manage that as the probability of getting such is less

 

[Peter_SSs]

Thank you so much peter

You're welcome.

 

[Sailadarohit]

Thanks Peter, This worked very well. Given a scenerio if the nike word is present in the 3rd or 4th or any other position how to update the formula

 

[Peter_SSs]

.. so the rules have changed again!!

If you can give a set of sample data containing all the different variations possible, including those already covered and any new ones, and the expected answer for each one in a form that I can copy to test with I will consider having a look at it.