HOW TO EXTRACT DATA TABLE LIST

[INCOMETAX111]

1. Arjuna (Terminalia arjuna) Bk. 90 mg 2. Kutaja Chhal Ghansatva (Holarrhena antidysenterica) St.Bk. 40 mg 3. Saptparni (Alstonia scholaris) Bk. 20 mg 4. Musta (Cyperus rotundus) Rt. 20 mg 5. Neem (Melia azadirachta) Sd. 20 mg 6. Ushira (Vetiveria zizanioides) Rt. 20 mg

I want to data list in cell by name parts and quantity as seperate through flash fill or any other option if not possible then through formula

 

[bosco_yip]

Formula solution.

1] Some adjustment making to the Source data (C7) and Criteria (H7 & H8) :

To remove the trailing space at the criteria H7 and H8

And, add the 2nd criteria H8 content inside the Source data C7 (show in red color).

Then

2] In I7, formula copied across to J7 and all copied down :

=TRIM(MID(SUBSTITUTE(" "&MID($C$7,SEARCH($H7,$C$7)+LEN($H7)+1,399)," ",REPT(" ",50)),COLUMN(A1)*50,COLUMN(A1)*50))