I can't seem to simply parse the first word of a column with the rest writing to a next new column.

[wittonlin]

I found a solution here. But I can't get it to stop after the first space.

E.g.

Col A   Col B Col C Col D Col E
Bob Scott   Bob Scott
Bob Joe Scott   Bob  Joe   Scott
Bob Joe Scott Jr.   Bob   Joe   Scott   Jr.
Indefinitely...

Ultimately, It would be great to handle it like this

Col A
Bob Scott
Bob Joe Scott
Bob Joe Scott Jr.

TO

Col A Col B
Bob Scott
Bob Joe Scott
Bob Joe Scott Jr.

It would also be great to be able to select the column to parse the first word to THAT column and the rest to the next column, inserted as new.

Sub parser<code>()
 </code>   Dim N As Long, wf As WorksheetFunction
    Set wf = Application.WorksheetFunction
    N = Cells(Rows.count, "A").End(xlUp).Row
    Dim i As Long, j As Long, k As Long
    For i = 1 To N
        ary = Split(wf.Trim(Cells(i, "A").Text), " ")
        k = 2
        For j = LBound(ary) To UBound(ary)
            Cells(i, k).Value = ary(j)
            k = k + 1
        Next j
    Next i
End Sub

Embarrassingly, This is how I got it to work!

Sub ParseFirstWordColumnAToColARestToColB()
Columns("B:D").Insert Shift:=xlToRight
    Dim N As Long, wf As WorksheetFunction
    Set wf = Application.WorksheetFunction
    N = Cells(Rows.count, "A").End(xlUp).Row
    Dim i As Long, j As Long, k As Long
    For i = 1 To N
        ary = Split(wf.Trim(Cells(i, "A").Text), " ")
        k = 2
        For j = LBound(ary) To UBound(ary)
            Cells(i, k).Value = ary(j)
            k = k + 1
        Next j
    Next i
Columns("A").Delete
Call CombineColumnBandC2BDeleteC
End Sub

 

[Rick Rothstein]

Sub parser()
    Dim N As Long, wf As WorksheetFunction
    Set wf = Application.WorksheetFunction
    N = Cells(Rows.count, "A").End(xlUp).Row
    Dim i As Long, j As Long, k As Long
    For i = 1 To N
        ary = Split(wf.Trim(Cells(i, "A").Text), " ", 2)
        k = 2
        For j = LBound(ary) To UBound(ary)
            Cells(i, k).Value = ary(j)
            k = k + 1
        Next j
    Next i
End Sub

Make the small change I show in red above.

 

[wittonlin]

Perfect Rick! I knew it was a small change. I just wish I would have came here before trying to make it work for 2 hours!

Now I have it down to...

Sub ParseFirstWordColumnAToColARestToColB()
Columns("B:C").Insert Shift:=xlToRight
    Dim N As Long, wf As WorksheetFunction
    Set wf = Application.WorksheetFunction
    N = Cells(Rows.count, "A").End(xlUp).Row
    Dim i As Long, j As Long, k As Long
    For i = 1 To N
        ary = Split(wf.Trim(Cells(i, "A").Text), " ", 2)
        k = 2
        For j = LBound(ary) To UBound(ary)
            Cells(i, k).Value = ary(j)
            k = k + 1
        Next j
    Next i
Columns("A").Delete
End Sub

Now if I could just get it to work by selecting any column to parse the first word to THAT column and the rest to the next column, inserted as new column. That would be wonderful. If it's too much work np.

 

[Rick Rothstein]

Now if I could just get it to work by selecting any column to parse the first word to THAT column and the rest to the next column, inserted as new column. That would be wonderful. If it's too much work np.

I have given you a different macro which will ask you to pick any cell in the column you want to start the parsing of the names in...

[table="width: 500"]
[tr]
	[td]Sub ParseFirstWordSelectedColumnSecondWordToNextColumn()
  Dim R As Long, Col As Range, Data As Variant
  Set Col = Application.InputBox("Select any cell in the column to parse to...", Type:=8)
  If Not TypeOf Col Is Range Then Exit Sub
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  For R = 1 To UBound(Data)
    Cells(R, Col(1).Column).Resize(, 2) = Split(Data(R, 1), " ", 2)
  Next
End Sub[/td]
[/tr]
[/table]

 

[wittonlin]

Thank you! This is kinda cool, though I don't have a need yet. Very possibly though!

I meant to be able to select the column to apply the macro. Say (firstname middlename lastname) are in column D so I'd want (firstname) in D and (middlename lastname) in column E, just inserted as a new column.

 

[Rick Rothstein]

I meant to be able to select the column to apply the macro. Say (firstname middlename lastname) are in column D so I'd want (firstname) in D and (middlename lastname) in column E, just inserted as a new column.

Ah, sorry, I did not understand that from my reading of your original message. The code I posted can be modified quite easily to do that. Now, the question it will ask you is to select any cell in the column containing your names and then it will automatically insert a column and split the names between the original column and the newly inserted one.

[table="width: 500"]
[tr]
	[td]Sub ParseFirstWordSelectedColumnSecondWordToNextColumn()
  Dim R As Long, Col As Range, Data As Variant
  Set Col = Application.InputBox("Select the column with your names...", Type:=8)
  If Not TypeOf Col Is Range Then Exit Sub
  Set Col = Col(1).EntireColumn
  Col.Offset(, 1).Insert
  Data = Range(Cells(1, Col.Column), Cells(Rows.Count, Col.Column).End(xlUp))
  For R = 1 To UBound(Data)
    Cells(R, Col.Column).Resize(, 2) = Split(Data(R, 1), " ", 2)
  Next
End Sub[/td]
[/tr]
[/table]

 

[wittonlin]

Wow! Thanks Rick! I was just going to mention that and see you already fixed it!