swop round data in a cell

[Angel Eyes]

I have a list of names, surname followed by the christiam name in one cell,
EG; Smith, Tom. Is there a way of highlighting all cells in this format so
that they are then displayed as christian name followed by surname.

 

[Gary''s Student]

Hilight the cells and run this macro:

Sub nameswapper()
For Each r In Selection
s = Split(r.Value, ", ")
r.Value = s(1) & " " & s(0)
Next
End Sub


Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Rich]

=PROPER(B1&" "&REPLACE(A1,FIND(B1,A1),LEN(B1),""))

 

[Rick Rothstein \(MVP - VB\)]

I was going to post this separately, but your write up is so nicely done, I
figured I would just post my alternate code here instead.<g> While I haven't
time tested it, usually Split is slower than Mid, InStr and Len (even when
all of them are combined) which could make a difference in a large loop;
hence the reason I am posting this. As I said, I didn't time test it, so the
difference could be large or it may be quite small; but, whichever, I figure
it is always nice to see alternatives.

Sub NameSwapper()
Dim R As Range
For Each R In Selection
R.Value = Mid(R.Value & " " & R.Value, InStr(R.Value, ", ") + 2,
Len(R.Value) - 1)
Next
End Sub

I would point out that both your code and mine will fail if the first name
is something like Mary Anne (I have a friend with this first name). I can
modify my code to use InStrRev instead of InStr to handle this, but then the
code would fail with a last name like Della Rossa (I have a friend with this
last name).

Rick