Change order of data in a cell
 

Is it possible to write a macro that will change cells that have "Last Name,
First Name, Middle Initial" to "First Name, Middle Initial, Last Name" ?
Thank you for your help.
David

Change order of data in a cell
 

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

"Ron Rosenfeld" wrote:

On Tue, 6 Jan 2009 13:06:06 -0800, David
wrote:

Is it possible to write a macro that will change cells that have "Last Name,
First Name, Middle Initial" to "First Name, Middle Initial, Last Name" ?
Thank you for your help.
David

Yes it is.

Are you certain the data format is exactly as you have provided?
i.e. All cells have exactly three segments separated by commas.

Are you certain you want to have commas separating the segments in the result?

At its simplest, you could use something like:

==============
Option Explicit
Sub FixName()
Dim c As Range
Dim s
For Each c In Selection
s = Split(c.Text, ",")
c.Value = s(1) & ", " & s(2) & ", " & s(0)
Next c
End Sub
==================

Of course, since your original and result formats are identical, you'd have to
set up a routine to ensure that you didn't run the macro twice on the same
cell.
--ron

Change order of data in a cell
 

On Wed, 7 Jan 2009 06:24:14 -0800, David
wrote:

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