Splitting data from a single cell into multiple cells

**gselfr** · 06-22-2011, 03:05 PM

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.

**ChemistB** · 06-22-2011, 03:26 PM

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?

**stanleydgromjr** · 06-22-2011, 03:35 PM

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.

**gselfr** · 06-22-2011, 05:35 PM

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

**stanleydgromjr** · 06-22-2011, 07:13 PM

gselfr,

You are very welcome.

Glad I could help.

Thanks for the feedback.

Come back anytime.

**onsid** · 02-15-2013, 02:06 AM

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

**FDibbins** · 02-15-2013, 02:09 AM

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.

**onsid** · 02-15-2013, 04:33 AM

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

**ChemistB** · 02-15-2013, 08:41 AM

@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.

**stanleydgromjr** · 02-15-2013, 08:58 AM

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.