Reverse data in cell

[lioninstreet]

I have what most likey isnt a unique problem, but since I havent found an answer on the forum, here goes. Please be kind to a noob. This is my first post with Excel Forum

I am getting a mailing list that comes with data in a single cell as follows. (lastname firstname). Sometimes there is also a middle name in the single cell. I need to change the data as follows.

First I'd like to manipulate the the data in the cell and put the last name at the end of the data instead of the begining. As an example, the orignial cell data comes looking like (lastname firstname middlename) in a single cell and I need to convert to (firstname middlename lastname).

Second I'd like to break this data out so I have a new cell containing only the first name data and another cell containing the middle and last name data.

Is that possible to do? I certainly appreciate the time and comments!

Thanks

[khamilton]

Assuming your names start in A1 put this in B1.

Please Login or Register  to view this content.

[vlady]

after applying the formula @khamilton(Forum Contributor)

your second querry (break in to column)

Select the list of names, go Edit-->text to columns
in the dialogue make sure the delimited radio button is selected

[khamilton]

@ vlady Sorry didn't read down far enough for that.

[GeneralDisarray]

Ok, to do this without Text to Columns (which is a fine method) use the following (because sometimes it's nice to use worksheet functions only):

For the First Name:

Please Login or Register  to view this content.

For the Middle Name:

Please Login or Register  to view this content.

For the Last Name:

Please Login or Register  to view this content.

IF you wanted to combine the last 2 names...just concatenate the formulas with & " " & (will also put a space between them).