| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I invert all of my cell entries ?
Can someone please explain how to invert all the cel entries in an excel
spread sheet. i.e. John and Sally Hancock becomes Hancock, John and Sally? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do I invert all of my cell entries ?
One way:
=RIGHT(A1,LEN(A1)-FIND(" ",A1,10))&", "&LEFT(A1,FIND(" ",A1,10)) But this only works if all your data is exactly like the example below. Check out http://www.cpearson.com/excel/FirstLast.htm for more samples. HTH, JP On Apr 5, 8:16*am, Kimmie23 wrote: Can someone please explain how to invert all the cel entries in an excel spread sheet. i.e. John and Sally Hancock becomes Hancock, John and Sally? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do I invert all of my cell entries ?
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))&",
"&LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))-1) "Kimmie23" wrote: Can someone please explain how to invert all the cel entries in an excel spread sheet. i.e. John and Sally Hancock becomes Hancock, John and Sally? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do I invert all of my cell entries ?
Give this formula a try...
=MID(A1&", "&A1,FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,LEN(A1)+1) Rick "Kimmie23" wrote in message ... Can someone please explain how to invert all the cel entries in an excel spread sheet. i.e. John and Sally Hancock becomes Hancock, John and Sally? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do I invert all of my cell entries ?
Of course, my newsreader (and I'm guessing others) split the line at a blank
space. Here is the same formula with a forced break so that your newsreader won't break it at the space... =MID(A1&", "&A1,FIND("|",SUBSTITUTE(A1," ","|", LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,LEN(A1)+1) Rick "Rick Rothstein (MVP - VB)" wrote in message ... Give this formula a try... =MID(A1&", "&A1,FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,LEN(A1)+1) Rick "Kimmie23" wrote in message ... Can someone please explain how to invert all the cel entries in an excel spread sheet. i.e. John and Sally Hancock becomes Hancock, John and Sally? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to invert 2 series? | Charts and Charting in Excel | |||
| Invert a column | Excel Worksheet Functions | |||
| How do I invert text (abc--cba) | Excel Worksheet Functions | |||
| Invert layout | Excel Discussion (Misc queries) | |||
| Invert Text | Excel Discussion (Misc queries) | |||
Linear Mode
