+ Reply to Thread
Results 1 to 10 of 10

Splitting data from a single cell into multiple cells

  1. #1
    Registered User
    Join Date
    06-22-2011
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    2

    Unhappy Splitting data from a single cell into multiple cells

    Hello,

    I hope the title makes sense. I want to split data from a single cell into multiple cells. Each cell on a row has different items listed. Some have 14 items, other 2 or 3, in the same cell. The only item seperating each listed data is a semicolumn. I did "text-to-columns" but I can only separate the first item listed. Here's an example of what it looks like.

    I've been searching everywhere for the last hour and just can't find anything. I appreciate any help you can provide.

    Thank you.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Splitting data from a single cell into multiple cells

    You didn't include your example but Text to Columns should work. Select Delimited and then "Next" then choose "Semicolon". Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Splitting data from a single cell into multiple cells

    gselfr,

    Welcome to the Excel Forum.


    I assume that your raw data is in column A, beginning in cell A1.


    Detach/open workbook SplitData - gselfr - EF781229 - SDG12.xlsm and run the SplitData macro.



    If you want to use the macro on another workbook:


    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


    1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
    2. Open your workbook
    3. Press the keys ALT + F11 to open the Visual Basic Editor
    4. Press the keys ALT + I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
    7. Press the keys ALT + Q to exit the Editor, and return to Excel
    8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


    Please Login or Register  to view this content.

    Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


    Then run the SplitData macro.
    Attached Files Attached Files
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  4. #4
    Registered User
    Join Date
    06-22-2011
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    2

    Smile Re: Splitting data from a single cell into multiple cells

    Thank you ChemistB and Stanley!

    @ChemistB- the regular text-to-Columns wouldn't work with so many semicolons.

    @Stanley-- that macro is fantastic! I have to make sure to make time to play with macros. It worked perfectly! Thank you soooo much!! :D

    -- and sorry for not including the sample- brain freeze on my part.

    Thank you both for being so fast in responding!! :D

  5. #5
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Splitting data from a single cell into multiple cells

    gselfr,

    You are very welcome.

    Glad I could help.

    Thanks for the feedback.

    Come back anytime.

  6. #6
    Registered User
    Join Date
    01-26-2013
    Location
    Middle east
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Splitting data from a single cell into multiple cells

    Hi Stan,
    This is great.
    I was looking for something like this, but with a little twist.
    I am looking for a solution that will place the new "broken" text to start in a different cell on the same row, leaving the original text as-is (lets say in column B or C, etc.)
    Can you please try and help me out?
    Many thanks in advance

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,244

    Re: Splitting data from a single cell into multiple cells

    onsid...
    Welcome to the Forum.

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  8. #8
    Registered User
    Join Date
    01-26-2013
    Location
    Middle east
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Splitting data from a single cell into multiple cells

    Applogies to all. Will comply with the rules !
    In my defence, I thought the question was very relevant to the same thread...

  9. #9
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Splitting data from a single cell into multiple cells

    @ChemistB- the regular text-to-Columns wouldn't work with so many semicolons.
    Glad Stan's macro worked for you but just so others reading this don't get the wrong idea. I do not know if there is a limit to the number of delimiters but if there is, it's a very large number. "Text to Columns" would have no issue separating 14 semicolons.

  10. #10
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Splitting data from a single cell into multiple cells

    onsid,

    Please create your own New Post, with an appropriate title, instructions, and a link to this current post.

    In your New Post, please attach a workbook containing your raw data, and the results (manually created by you) that you are looking for.

    To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.


    Then send me a Private Message, with a link to your New Post, and I will have a look.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1