splitting cells
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: splitting cells

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    London
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,833
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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.

  3. #3
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    New Member
    Join Date
    Apr 2002
    Location
    London
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com