split cell

I have a cell that has a name such Adam Bean. I want to right a formula that will make this Bean, Adam. I currently have formula's in two cells. The first cell I have =Right(L2,LEN(L2)-find(" ",L2)) and in the other cell I have =LEFT(L2,LEN(L2)-FIND(" ",L2)). Is there a way to combine those formulas into one cell? Also if the name is very short, I get part of the other name. If it is very long then I don't get all of the name.

If this is confusing I can attach a spreadsheet.

Thanks

Try this, splitting it based on the space between names.

It may get problematic if you have 2 word names, or Jr. Sr. etc..

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

Perfect, Thanks!

You're welcome.