Separate first and last name useing formula, not Concatenate

**Mia79** · 10-22-2012, 11:36 AM

Hi,

I am trying to figure out a formula that will separate first and last name into two columns (right now they are in one column) but not using the concatenate function.
Some of these names have middle initials and some do not. I need to change the order or the last name, first name into first name, last name.
If I use concatenate byseparating the names on a space, I get three columns and my second column somewhere has the middle name and somewhere the last name.
I had someone show me the formula once - but it was complicated to remember by using only once, and I did not save the file in which I had the formula

I only remember we used a formula by which we counted the characters from right and from left up to space. So, the number of character was variable and the space was used as a stopping point. We separated that one column into two (one for last and one for first name) and then we merged them again - only in different order.
If someone can tell me how to do this again, I would be very grateful!

I hope I was clear about what I am trying to do - please let me know if I was not.
M.

**:) Sixthsense :)** · 10-22-2012, 11:38 AM

It will be more clear if you attach a sample workbook with some example data with expected output also.

**Mia79** · 10-22-2012, 12:18 PM

excel_image.png

here is what I am trying to do

**tigeravatar** · 10-22-2012, 12:40 PM

Mia79,

Welcome to the forum!
To get the result shown in your column D without helper columns, give this formula a try:

Please Login or Register  to view this content.

**Mia79** · 10-22-2012, 01:08 PM

Tigeravatar,

thanks so much! it worked. when you have time, would you mind explaining the steps? I want to figure it out so I can do it on my own.
M.

**tigeravatar** · 10-22-2012, 01:24 PM

Select the cell containing the formula, go to the Formulas tab on the ribbon, click "Evaluate Formula" and it will walk you through the steps.

**Cutter** · 10-22-2012, 08:41 PM

@ Mia79

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.