0

In Excel 2011, I have one column containing first names of individuals and couples ("Donald R. and Jessie" or "Karen S." or "Jerry and Sheila"). I need them separated so that each piece of the name is in a separate column while removing periods, "and", as well as all the spaces. Help!

0

2 Answers 2

0

I believe the best method, since a formula would be extensive and probably not possible, is a series of steps.

  1. Go to Data, Text to Columns. Set Delimited and click Next. Then put a check in the "Space" box and click Finish. Now all of the names are separated out.

  2. Do a Find/Replace for "and" and replace it with nothing. This removes all the "and" values. Do the same for periods.

  3. Do a sort on Column B and this will show you the "R." and "S." initials. Clear them. Do not delete the cell, just the values. Do another sort on Column C and D and do the same steps.

  4. Do a sort on Column B and copy all of the first names listed to the end of column A. Do this for Column C and D.

You may need to continue beyond column D, just have to look at the data and see.

2
  • I walked through a few of these steps to see if I could get it to work. I need to have all first names in one column, middle initials in another and last names in another. Because not all of the names follow the same format, using Text to Columns sorts them all out in different columns. The other thing I ran into is that when I search and replace "and", it also removes parts of names that include those three characters, like Sandra. Other thoughts?
    – Angie
    Oct 22, 2013 at 17:58
  • On the replace, click "Options" and do "Match entire cell contents" to match only AND and not Sandra. Because of what you need and the difference in formatting I do not believe you can really automate this procedure. The Text to Columns and sorting is the only way but will still require much manual manipulation.
    – Travis
    Oct 22, 2013 at 18:38
0

Assuming this starts in A1 and then A2 and then A3 (downwards)

In Cell B1

=SUBSTITUTE(TRIM(LEFT(A1,FIND(" ",A1,1)-1)), ".","")

in Cell C1

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))), ".","")

This will take the first and last word as per your examples.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .