Multiple numbers seperated by commas in a single cell

[abe10]

Hello,
Hope you can help me. I have multiple numbers in a single cell seperated by commas. I would like to have each number listed in it;s own cell but not across but down Example listed;

(Canada) (1,2,3,4,5) () indicates a cell

I would like to end up with

(Canada) (1)
(Canada (2)
(Canada) (3)
(Canada) (4)
(Canada) (5)

[loginjmor]

Hi -

If you just have a very few numbers, the easiest is to retype them. If you have a lot of numbers and they are all sequential, it's still probably easier to type in the first couple of the series and copy down, letting Excel autofill the range. However, since you are at this forum, I am guessing it's not that simple. So do the following:

1. Highlight the cell with the number string in it.
2. Under the Data Tab, select the Text to Columns tool. This will walk you through a process to parse out the data using commas as the delimiter between each number.
3. Once all of your data is in columns, you will want to transpose it into one single column with multiple rows. Use the TRANSPOSE function.
4. The TRANSPOSE function is just a little tricky in that you follow Excel's help to write the equation, but then highlight the formula cell plus as many rows as you have columns to transpose.
5. While you are editing the formula AND you have your range highlighted, press the Ctrl+Shift+Enter keys at the same time. This creates an Array Formula. You can look up more help on that in Excel's help. But this should fill your rows with the columns of data you parsed out earlier. I had to play with this two or three times and look at the help to get it right but it's not too terrible.
6. Highlight the new column of numbers and copy them. Then Paste Special back to the exact same location but specify you want to paste the Values. This creates your column of numbers without the transpose formula. Now you can delete the other columns of data in your spreadsheet you no longer need.
7. Move your new column of numbers wherever you want them to be and put "Canada" in a cell immediately to the left of the first number and copy it down.

That should do what you are wanting. Hope this helps.