splitting cells

mekon

New Member
Joined
Apr 14, 2002
Messages
2
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
On 2002-04-15 07:05, mekon wrote:
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

Did you Data|Text to Columns where you use "-" as delimiter? You could also record your actions into a macro.
 
Upvote 0
Jeez, the one time I was going to suggest a non VBA approach and AA beats be to it.

Tom Urtis is correct, it is like speed chess at times.

One thing I would suggest as well though is that you do a search and replace all for " - " and stick in some character like "|" or "#". Then do a "text to columns" using "|" or whatever symbol you choose. This will get rid of those pesky spaces before the words.

HTH
 
Upvote 0
fantasically simple. I've got so used to using formulas in excel that I've obviously forgotten some of it's other functions

thanks guys

M
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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