hi all.
I have a column of text cells showing product categories, with the following pattern:
category1 - category2 - category3
eg:
"chocolates and sweets - sweets - sugar sweets"
"grocery - cakes & bread - cakes"
"household - laundry - washing liquid"
I need to be able to split the contents of each cell into 3 new cells showing the 1st, 2nd and 3rd categories. the categories have varying text lengths and sometimes contain more than one word. they are separated in the parent cell by the "-" character.
is there a formula/macro? that could do this
(i.e by saying "split everything to the left of the first "-" in the cell, split everything between the first and second "-", and split everything to the right of the second "-")
hope this makes sense. thanks in advance for any assistance
M
I have a column of text cells showing product categories, with the following pattern:
category1 - category2 - category3
eg:
"chocolates and sweets - sweets - sugar sweets"
"grocery - cakes & bread - cakes"
"household - laundry - washing liquid"
I need to be able to split the contents of each cell into 3 new cells showing the 1st, 2nd and 3rd categories. the categories have varying text lengths and sometimes contain more than one word. they are separated in the parent cell by the "-" character.
is there a formula/macro? that could do this
(i.e by saying "split everything to the left of the first "-" in the cell, split everything between the first and second "-", and split everything to the right of the second "-")
hope this makes sense. thanks in advance for any assistance
M