Convert FirstName LastName to LastName,FirstName using MID,FIND, LEN Function 2

[JimStrand]

I am using a combination of excel functions(MID, FIND, LEN) to convert cell A1 from FirstName LastName to LastName,FirstName.

A1_______________________C1
FirstName LastName LastName,FirstName

Formula in C1
=MID(A1&","&A1,FIND(" ",A1)+1,LEN(A1))

What I need is a space before the FirstName

Any suggestions for revising the function to obtain this result? Many Thanks.

Jim

 

[SkipVought]

Hi,

How about this name?

Jo Bob Van der Voort

How would you LOGICALLY determine first and last?

But if ALL your names are and will forever be only two words delimited by ONE SPACE, then...
[tt]
=LEFT(A3,FIND(" ",A3)-1)&","&RIGHT(A3,LEN(A3)-FIND(" ",A3))
[/tt]

BTW, did you really want to limit the solution to FIND(), LEN() & MID()? It might have been more appropriate to simply specify the use of spreadsheet formulas, not specific formulas.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[JimStrand]

Thanks Skip. This function returns FirstName, LastName . I'm looking for LastName, First Name
The space is there before but the Names are in opposite order.

Jim

 

[SkipVought]

And...?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

I'm looking for LastName, First Name" - Just reverse Skip's formula:
[tt]
=RIGHT(A3,LEN(A3)-FIND(" ",A3)) & ", " & LEFT(A3,FIND(" ",A3)-1)
[/tt]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Andy, I thought the OP could figure that out. I wouldn't insult his intelligence.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[JimStrand]

Thank you gentleman. I appreciate you help, and sarcasm.

 

[Andrzejek]

Sorry Skip. And sorry Jim.
I did not mean to insult anyone here

And I’ve got an un-deserved star...

Have fun.

---- Andy

There is a great need for a sarcasm font.