If I got column of names
John Doe

And what to change it to
Doe, John

Is thier a way of doing this?

DJ

Try something like


Dim S As String
Dim Pos As Integer
S = Range("A1").Text
Pos = InStr(1, S, " ")
S = Mid(S, Pos + 1) & ", " & Left(S, Pos - 1)
Range("A1").Value = S


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Duncan_J" wrote in message
...
If I got column of names
John Doe

And what to change it to
Doe, John

Is thier a way of doing this?

DJ

Thanks Jim but couldn't get the function to work...
Thanks Trent. it works for the first one but it won't go to the next one and
the whole coulmn

"Duncan_J" wrote:

If I got column of names
John Doe

And what to change it to
Doe, John

Is thier a way of doing this?

DJ

Thanks Guys I got'em to work... Thanks for the help

"Duncan_J" wrote:

If I got column of names
John Doe

And what to change it to
Doe, John

Is thier a way of doing this?

DJ

Duncan, I forgot to add that it is selection dependent (i.e., the user must
manually select the range before executing the procedure).
Otherwise, you (the developer) has to give the procedure a way to terminate.
E.g.,
ActiveCell.SpecialCells(xlLastCell).Row
returns the last row's index of the ActiveSheet.
Thus:
Dim lngCurrentRow as Long
Range("<RangeID").Select 'E.g. Range("A1").Select
Do
lngCurrentRow = lngCurrentRow + 1
ActiveCell.Value = FormatName(ActiveCell.Value) 'This is from Jim
Thomlinson's solution
ActiveCell.Offset(1,0).Select 'This increments the cursor
Loop until lngCurrentRow = ActiveCell.SpecialCells(xlLastCell).Row

--
Trent Argante


"Duncan_J" wrote:

Thanks Jim but couldn't get the function to work...
Thanks Trent. it works for the first one but it won't go to the next one and
the whole coulmn

"Duncan_J" wrote:

If I got column of names
John Doe

And what to change it to
Doe, John

Is thier a way of doing this?

DJ