Dynamic split

image
#60,832 in Grocery & Gourmet Foods
#1097 in Herbal Tea
#1272 in Green Tea
#4 in Black Tea
this is the value of one cell in a excel.i want to split it my result will be store in 8 different columns.like
Rank_1-60832, Rank_2-1097,Rank_3-1272,rank_4-4,category_1-Herbal Tea,category_2-Herbal Tea,
category_3-Green Tea,category_4- Black Tea.
[all time 4 types of rank may not come, sometimes 2 type or 3 type of Rank may come.
like #27,719 in Grocery & Gourmet Foods
#599 in Green Tea,
in this case only Rank_1, Rank_2 and category_1,category_2 will be filled resat other columns will be blank.]
how can i do this?
i am attaching my excel file also.rank.xlsx (8.4 KB) .