Hi Todd,

You want to use the InStr function again to find the blank in sStr. I
changed the column from your code.

Sub test()
numcount = Application.WorksheetFunction.CountA(Sheets(1).Ran ge("A:A"))
For Each cell In Sheets(1).Range("A1:A" & numcount)
If Not IsEmpty(cell) Then
If InStr(cell, ",") Then
sStr = Right(cell, Len(cell) - InStr(cell, ","))
'MsgBox sStr
If InStr(sStr, " ") Then
sStr = Trim(sStr)
newStr = Left(sStr, Len(sStr) - InStr(sStr, " ") + 2)
End If
cell.Value = Trim(newStr) & " " & Trim(Left(cell, InStr(cell,
",") - 1))
End If
Else
Exit For
End If
Next
End Sub

HTH, Greg

"Todd Huttenstine" wrote in message
...
The below code looks in a specified dynamic range and for
each item(Persons name) in the range, it removes the comma
and then flip flops the name to FIRST LAST from LAST
FIRST. It works perfectly for people who do not have a
middle initial but for people who have a middle initial,
it keeps it there. For example the code as it is now will
convert the name Alexander, Tim into Tim Alexander.
However a person who has a middle initial is different.
If Tims name were Alexander, Tim C. the code would convert
it to Tim C. Alexander. I want his name to to be like the
rest which would be Tim Alexander.


How would I modify the below code to accomplish this?

numcount = Application.WorksheetFunction.CountA(Sheets
("Converted Data").Range("F:F"))
For Each cell In Sheets("Converted Data").Range("F2:F" &
numcount)
If Not IsEmpty(cell) Then
If InStr(cell, ",") Then
sStr = Right(cell, Len(cell) _
- InStr(cell, ","))
cell.Value = Trim(sStr) & " " & _
Trim(Left(cell, _
InStr(cell, ",") - 1))
End If
Else
Exit For
End If
Next



Thank you

Todd Huttenstine