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?

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?

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?

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?

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?