splitting cells

mekon · Apr 15th, 2002, 08:05 AM

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

Aladin Akyurek · Apr 15th, 2002, 08:09 AM

Quote:

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.

Mark O'Brien · Apr 15th, 2002, 08:12 AM

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

mekon · Apr 15th, 2002, 08:27 AM

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

Chris Davison · Apr 15th, 2002, 01:37 PM

Mekon... is that from the 2000AD comic ?

Apr 15th, 2002, 08:12 AM	#3
Mark O'Brien MrExcel MVP Join Date: Feb 2002 Location: Columbus, OH, USA Posts: 3,519	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 __________________ Mark O'Brien Columbus Ohio Celtic Supporters Club

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 15th, 2002, 08:05 AM	#1
mekon New Member Join Date: Apr 2002 Location: London Posts: 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

Apr 15th, 2002, 08:27 AM	#4
mekon New Member Join Date: Apr 2002 Location: London Posts: 2	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

Apr 15th, 2002, 01:37 PM	#5
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Mekon... is that from the 2000AD comic ?