Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Apr 2006
    Location
    Dublin, Ireland, Republic of
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Comma delimited text to Colums (Excel2003)

    Can anyone tell me how to transform a comma delimited data string that is in a cell (A1) and copy each individual word (as bound between commas) into their own column. A sample copy of the data is below. In this case I want to put "Brilliant Silver Metallic" into (B1), "2003(03) into (C1) etc....

    Brilliant Silver Metallic, 2003 (03), 54025 miles, Satellite Navigation, Air Conditioning, Alloy Wheels, CD Autochanger, Electric Seats, Parking Distance Control - Front and Rear, 2 Owners, 5 Speed, Airbags, Auto Headlights, Central Locking, Climate Control, Command Navigation, Computer, Cruise Control, Electric Windows, Full Leather, Heated Mirrors, Heated Seats, Metallic, Mobile Phone Prep, Multi Steering Wheel Control

  2. #2
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Vancouver, BC
    Posts
    100
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comma delimited text to Colums (Excel2003)

    Hi,

    When everything is in a single column, you select the column, then go to Data / Text To Columns. You can check off the Delimited radio button, then in the next screen choose the delimiting factor. That splits the data up very nicely.

    Hope that helps.

    John

  3. #3
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Comma delimited text to Colums (Excel2003)

    Cell B1, enter the formula and copy across :

    =TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",500)),COLUMN(A:A)*500-499,500))

    Regards
    Bosco

Posting Permissions

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