Hi Bob,

I tried to copy to the code. All of the following part comes out in red:


If Mid(.Cells(i, j).Value, 1) = " " Or _
Mid(.Cells(i, "A").Value, j, 1) = UCase(Mid(.Cells(i,
"A").Value, j, 1)) Then
.Cells(i, "A").Value = Right(.Cells(i, "A").Value, k - j
+ 1) & _
", " & Left(.Cells(i, "A").Value, j - 1)



What do I do wrong?


Felix


"Bob Phillips" wrote:

Here's some code

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long, j As Long, k As Long
Dim iLastRow As Long
With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow
k = Len(.Cells(i, "A").Value)
For j = k To 2 Step -1
If Mid(.Cells(i, j).Value, 1) = " " Or _
Mid(.Cells(i, "A").Value, j, 1) = UCase(Mid(.Cells(i,
"A").Value, j, 1)) Then
.Cells(i, "A").Value = Right(.Cells(i, "A").Value, k - j
+ 1) & _
", " & Left(.Cells(i, "A").Value, j - 1)
Exit For
End If
Next j
Next i

End With

End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Felix" wrote in message
...
I have a huge list of names in either of the following ways:

PeterSmith or
Peter Smith

Is there an easier way (other than manually switch) to have the list in
the
order like

Smith, Peter or at least
Smith Peter ?

The only way I can think of is to somehow recognize the capitals...

Thank you

Felix