how do I invert all of my cell entries ?

[Kimmie23]

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?

 

[JP]

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

 

[Teethless mama]

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))&",
"&LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))-1)

 

[Rick Rothstein \(MVP - VB\)]

Give this formula a try...

=MID(A1&", "&A1,FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,LEN(A1)+1)

Rick

 

[Rick Rothstein \(MVP - VB\)]

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