I have a worksheet that has multiple numbers (1234, 3425, 34234,etc.) in a 
single cell.  I need to separate these and create a new row for each one (ie. 
A1 1234, A2 3425, A3 34234, etc.).  Currently I'm having to insert columns, 
convert text to columns, transpose the data, then copy and paste the rest of 
the data from the other columns...Is there an easier way?

This might be a quicker work around for you.  Highlight and copy all the info 
on your sheet.  Open a blank "Notepad" and paste everything in there.

While it's in raw form in Notepad, put the cursor at the end of each entry 
and hit the Enter/Return key.  Do that until each of your entries is on it's 
own line on the Notepad document like so:

1234  
3425
34234

Now copy your numbers from Notepad and past into your Excel sheet, and your 
entries should each be in their own row.




"Booj" wrote:

> I have a worksheet that has multiple numbers (1234, 3425, 34234,etc.) in a 
> single cell.  I need to separate these and create a new row for each one (ie. 
> A1 1234, A2 3425, A3 34234, etc.).  Currently I'm having to insert columns, 
> convert text to columns, transpose the data, then copy and paste the rest of 
> the data from the other columns...Is there an easier way?

      If desired, send your file to my address below. I will only look if:
      1. You send a copy of this message on an inserted sheet
      2. You give me the newsgroup and the subject line
      3. You send a clear explanation of what you want
      4. You send before/after examples and expected results.


-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Booj" <Booj@discussions.microsoft.com> wrote in message 
news:FB132221-62EA-4838-BCE0-BE5A20DFD13C@microsoft.com...
>I have a worksheet that has multiple numbers (1234, 3425, 34234,etc.) in a
> single cell.  I need to separate these and create a new row for each one 
> (ie.
> A1 1234, A2 3425, A3 34234, etc.).  Currently I'm having to insert 
> columns,
> convert text to columns, transpose the data, then copy and paste the rest 
> of
> the data from the other columns...Is there an easier way? 

On Thu, 27 May 2010 14:09:41 -0700, Booj <Booj@discussions.microsoft.com>
wrote:

>I have a worksheet that has multiple numbers (1234, 3425, 34234,etc.) in a 
>single cell.  I need to separate these and create a new row for each one (ie. 
>A1 1234, A2 3425, A3 34234, etc.).  Currently I'm having to insert columns, 
>convert text to columns, transpose the data, then copy and paste the rest of 
>the data from the other columns...Is there an easier way?

You can do this easily with a VBA macro.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first select the cell(s) you wish to process.  Since
the split will be by rows, if you select multiple cells, they must be in the
same row.

Then <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

=====================================
Option Explicit
Sub ParseComma()
  Dim rg As Range
  Dim c As Range
  Dim i As Long
  Dim s As Variant

Set rg = Selection
    If rg.Rows.Count > 1 Then
        MsgBox ("Cannot select multiple Rows")
        Exit Sub
    End If

For Each c In rg
    s = Split(c.Value, ",")
    For i = 0 To UBound(s)
        c(i + 1, 1).Value = Trim(s(i))
    Next i
Next c

End Sub
===============================
--ron