Parsing multiple names stored in a single cell

[hemmi1]

I download a list of instructors, and find that the 'Class Instructor' field stores up to 8 name values in a single field. I now need to report on each trainer.

Ex: cell

Frost, Robert
Farmer, George
Heston, Bruce
Brando, Moonbeam

etc., and it's all in a single cell.
I find that applying TRIM leaves spaces between word, and formats the data to look flat like this:

Frost, RobertFarmer,GeorgeHeston, BruceBrando, Moonbeam.

I'd thought of writing a routine to step through the data, one character at a time, and figure out how to embed a comma, hyphen, or some character based on the ascii value of a capital letter (ascii "A"=65, "Z"=90),
but that's when I decided to get an independent opinion.

I've looked at the CLEAN() and Substitute() functions, but they don't seem suited for this.

It just sounds like there must be a better way.

If the entries were formatted with a character denoting the end of a name, I'd be ok, but this is proving a bit difficult.

Thanks for any comments.

 

[stirlsa]

I tried this method but nothing happened.

 

[magician11]

Thanks for that solution Rick!

Give this macro a try...
[TABLE="width: 500"]
<tbody>[TR]
[TD]

Sub PutNamesInIndividualCellsDownSameColumn()
  Dim Combined As Variant
  Combined = Split(Join(Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp))), vbLf), vbLf)
  With Range("A1").Resize(UBound(Combined) + 1)
    .Value = Application.Transpose(Combined)
    .EntireRow.AutoFit
  End With
End Sub

[/TD]
[/TR]
</tbody>[/TABLE]



HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (PutNamesInIndividualCellsDownSameColumn) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[stirlsa]

I tried this but nothing happens for me...

 

[Rick Rothstein]

I tried this but nothing happens for me...

There were three solutions posted in this thread... exactly which one did not work for you?

 

[stirlsa]

First I tried the 'text to columns" I wondered if it didn't work because I didn't know how to convey that the text is separated by spaces.
Then I tried the macro.

What I want to do is: take several words of text in a single cell (which are each separated by a space) and I want to put each of those words into its own cell.

I know it is just me... cannot determine what I am doing wrong.

Actually let me tell you my problem specifically. I have a list of female names that I want to put into a sign-in sheet. But the names are like this:
Smith, Mary Jane Johnson
The problem is that I want to get rid of the last word in each field because that last word is the maiden name.

 

[stirlsa]

Oh I see now - I just select 'space' instead of ALT + J.

 

[Rick Rothstein]

Actually let me tell you my problem specifically. I have a list of female names that I want to put into a sign-in sheet. But the names are like this:
Smith, Mary Jane Johnson
The problem is that I want to get rid of the last word in each field because that last word is the maiden name.

That is actually a different question than what was asked in this thread originally. I'll answer your question here, but you really should have started a new thread for it (it would be exposed to a wider pool of volunteers that way). Give this macro a try (note I assumed your names were in Column A starting on Row 1)...

Sub RemoveLastWord()
  Dim R As Long, Data As Variant
  Data = Range("[B][COLOR="#0000FF"]A[/COLOR][/B][B][COLOR="#FF0000"]1[/COLOR][/B]", Cells(Rows.Count, "[B][COLOR="#0000FF"]A[/COLOR][/B]").End(xlUp))
  For R = 1 To UBound(Data)
    Data(R, 1) = Left(Data(R, 1), InStrRev(Data(R, 1), " ") - 1)
  Next
  Range("[B][COLOR="#0000FF"]A[/COLOR][/B][B][COLOR="#FF0000"]1[/COLOR][/B]").Resize(UBound(Data)) = Data
End Sub

 

[stirlsa]

That is actually a different question than what was asked in this thread originally. I'll answer your question here, but you really should have started a new thread for it (it would be exposed to a wider pool of volunteers that way). Give this macro a try (note I assumed your names were in Column A starting on Row 1)...

Sub RemoveLastWord()
  Dim R As Long, Data As Variant
  Data = Range("[B][COLOR=#0000FF]A[/COLOR][/B][B][COLOR=#FF0000]1[/COLOR][/B]", Cells(Rows.Count, "[B][COLOR=#0000FF]A[/COLOR][/B]").End(xlUp))
  For R = 1 To UBound(Data)
    Data(R, 1) = Left(Data(R, 1), InStrRev(Data(R, 1), " ") - 1)
  Next
  Range("[B][COLOR=#0000FF]A[/COLOR][/B][B][COLOR=#FF0000]1[/COLOR][/B]").Resize(UBound(Data)) = Data
End Sub

Thank you! It works great!