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

New Member
Joined
Sep 7, 2022
Messages
39
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
Platform
  1. Windows
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.
 

Attachments

  • word_finding.jpg
    word_finding.jpg
    40.9 KB · Views: 12

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sailadarohit_1.xlsm
ABCD
1ActualAssumedWordPresent ActualWordPresent Assumed
2Dress, brand_nike_costly_new, StoreJeans, brand_nike_air_cheap_Used, Onlinebrand_nike_costly_newbrand_nike_air_cheap_Used
3Online, brand_puma_shirt, PaidStore, brand_puma_jeans, ReturnNULLNULL
4Offline, brand_nike_jeans, CardShirt, brand_nike_tshirtsbrand_nike_jeansbrand_nike_tshirts
5Dress, brand_moniker_costly_new, StoreJeans, brand_moniker_costly_newNULLNULL
6black_nike_problack_nike_betablack_nike_problack_nike_beta
7online,black_nike_problack_nike_airblack_nike_problack_nike_air
8offline,red_nike,pantsshop,red_nike_air,shirtsred_nikered_nike_air
9Dress, Hyderabad,brand_nike_costly_new, StoreJeans, Hyd, brand_nike_air_cheap_Used, Onlinebrand_nike_costly_newbrand_nike_air_cheap_Used
10Online, Banglore, Return, brand_nike_shirt, PaidStore, Chennai, brand_nike_jeans, Returnbrand_nike_shirtbrand_nike_jeans
11online,Jeans, Hyderabad,black_nike_proonline,Hyderabad,black_nike,shirtblack_nike_problack_nike
Sheet2
Cell Formulas
RangeFormula
C2:D11C2=IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT(A2,FIND(",",A2&",",SEARCH("_nike",A2))-1),",",REPT(" ",100)),100)),"NULL")
 
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,194
Members
453,021
Latest member
pingpong7117

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top