Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
swop round data in a cell
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
swop round data in a cell
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 -- Gary''s Student - gsnu200777 "Angel Eyes" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
swop round data in a cell
=PROPER(B1&" "&REPLACE(A1,FIND(B1,A1),LEN(B1),"")) "Angel Eyes" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
swop round data in a cell
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 "Gary''s Student" wrote in message ... 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 -- Gary''s Student - gsnu200777 "Angel Eyes" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do you round up a cell? | Excel Discussion (Misc queries) | |||
Sum and Round all in one cell | Excel Discussion (Misc queries) | |||
Swop rows for columns Excel or Word | Excel Discussion (Misc queries) | |||
round up in own cell | Excel Worksheet Functions | |||
How do I ROUND() round off decimals of a column dataset? | Excel Worksheet Functions |