Text Separation

[compman]

How do I get one name in a cell when I have both last and first name in one cell separated by a comma?

 

[Brian from Maui]

How do I get one name in a cell when I have both last and first name in one cell separated by a comma?

Go to Data/Text to columns, using the "," as the delimiter

 

[Mark W.]

Care to provide an example?

 

[DonkeyOte]

text to columns as mentioned

or use for forename

=left(a1,find(" ",a1)-1)

and surname

=mid(a1,find(" ",a1)+1,len(a1)-find(" ",a1))

 

[Mark W.]

Perhaps I'm being to literal, but I believe the OP asked, "How do I get one name in a cell...". I don't believe he's asking how to parse into multiple cells. I'm guessing that he wants to rearrange...

Powers, Austin

...to...

Austin Powers

Perhaps compman will provide an example so we won't have to guess the intent of his inquiry.

 

[DonkeyOte]

Regardless the code remains effective - just insert an & to merge both names.

not rocket science.

 

[Mark W.]

I prefer...

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

 

[DonkeyOte]

each to their own...

 

[Mark W.]

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

produces "AustinPowers,".

 

[DonkeyOte]

not quite sure whether you're trying to be anally retentive but I am sure as an MVP you're quite capable of entering &" "& within the formula.