vba to add comma after first word in each cell in column

[kit99]

Got names in column F, written like: Lennon John Winston.
Column holds headers.

Anyone that got a vba that can run through column F and add comma after the first word?
When name is Lennon-Smith John Winston the comma must be placed like this: "Lennon-Smith, John Winston".

Tried this:

    Columns("F:F").Select
    Selection.Replace What:=" ", Replacement:=", ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

but it adds comma after every word...

 

[ranman256]

Re: Help on vba to add comma after first word in each cell in column

Public Sub AddNameComma()
Dim vNam, vFirst, vLast
Dim i As Integer


Range("F2").Select
While ActiveCell.Value <> ""
   vNam = ActiveCell.Value
   i = InStr(vNam, " ")
   If i > 0 Then
     vLast = Left(vNam, i - 1)
     vFirst = Mid(vNam, i + 1)
   
      ActiveCell.Value = vLast & ", " & vFirst
   End If
   
   ActiveCell.Offset(1, 0).Select   'next row
Wend
End Sub

 

[kit99]

Re: Help on vba to add comma after first word in each cell in column

Public Sub AddNameComma()
Dim vNam, vFirst, vLast
Dim i As Integer


Range("F2").Select
While ActiveCell.Value <> ""
   vNam = ActiveCell.Value
   i = InStr(vNam, " ")
   If i > 0 Then
     vLast = Left(vNam, i - 1)
     vFirst = Mid(vNam, i + 1)
   
      ActiveCell.Value = vLast & ", " & vFirst
   End If
   
   ActiveCell.Offset(1, 0).Select   'next row
Wend
End Sub

Fantastic! Works great!
Thanks a lot for helping out.

 

[kit99]

Re: Help on vba to add comma after first word in each cell in column

Public Sub AddNameComma()
Dim vNam, vFirst, vLast
Dim i As Integer


Range("F2").Select
While ActiveCell.Value <> ""
   vNam = ActiveCell.Value
   i = InStr(vNam, " ")
   If i > 0 Then
     vLast = Left(vNam, i - 1)
     vFirst = Mid(vNam, i + 1)
   
      ActiveCell.Value = vLast & ", " & vFirst
   End If
   
   ActiveCell.Offset(1, 0).Select   'next row
Wend
End Sub

Hi again.
I've run into a little problem with your code...
If column to scan for some reason holds blank cells here and there, the code stops after finding the first blank one.
Is it possible to change the code in such a way that it runs all the way of used range in column F, even 'tho there may be blank cells in between?
Or perhaps you can put in range to scan based on another column? Column A will always hold data in every cell in used range.

 

[ranman256]

Re: Help on vba to add comma after first word in each cell in column

put an end row....

while activecell.row < 9000

 

[kit99]

Re: Help on vba to add comma after first word in each cell in column

put an end row....

while activecell.row < 9000

Problem solved!
Thanks again.