I appreciate all of the posts. Sometimes the names have a middle initial and
sometimes they do not. I volunteer at a VA and these are patients. The
system uses no spaces between commas but I can do a find and replace. I am
concerned about the middle names/middle initials/no middle anything!
Thank you again,
David
Well, you still haven't answered all of my questions, but you answered a few.
The following will convert the Selected Cells as you have described so far. As
written, for debugging purposes, it puts the results in the adjacent cell. You
may want to leave it this way, as it obviates the need to flag the cell as
already having been processed, or, once you get it working the way you want,
you could set rDest = c
It assumes that the Name has 0, 1 or 2 commas, so could be one of the
following:
Last Name
Last Name, First Name
Last Name, First Name, Middle
and rearranges it appropriately.
If it is not in that format, it will return a #VALUE! error.
====================================
Option Explicit
Sub FixName()
Dim c As Range
Dim rDest As Range 'could be the same as c or
'some other cell for debugging
Dim s
For Each c In Selection
Set rDest = c.Offset(0, 1)
s = Split(c.Text, ",")
Select Case UBound(s)
Case Is = 0
rDest.Value = Trim(s(0))
Case Is = 1
rDest.Value = Trim(s(1)) & "," & Trim(s(0))
Case Is = 2
rDest.Value = Trim(s(1)) & "," & _
Trim(s(2)) & "," & Trim(s(0))
Case Else
rDest.Value = CVErr(xlErrValue)
End Select
Next c
End Sub
============================
--ron
