you can use
=REPLACE(A1,1,FIND(" ",A1),"")&", "&LEFT(A1,FIND(" ",A1)-1)
only if each cell contains exactly two names seperated by a space. An
explanation may be in order as to what this formula does. FIND returns a
number from the beginning to the " " (using Jane Doe you get 5). REPLACE
replaces chars 1-FIND with nothing (outputing only the last name). The & is
how you add strings together (I inserted ", " to get the output you wanted).
LEFT returns the text from 1-FIND (using Jane Doe you get Jane). If you
don't use -1 in the LEFT function you would get a space at the end ("Doe,
Jane " as apposed to "Doe, Jane").
"Red Lamps" wrote:
Is there a formula that manipulates data from "Jane Doe" to "Doe, Jane"?
|