Is there a formula that manipulates data from "Jane Doe" to "Doe, Jane"?

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"?

=RIGHT(A1,LEN(A1)-FIND(" ",A1))&", "&LEFT(A1,FIND(" ",A1)-1)

also works. LEN is the length of the string, so instead of subbing "" you
just pull the right name off the orginial string. In "Jane Doe" LEN is 8 and
FIND is 5, so RIGHT takes the last 3 digits (Doe).

"Sloth" wrote:

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"?

You have no idea how much I appreciate you! What a time saver!

Thank you so much!

"Sloth" wrote:

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"?