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.

 

[Runsk1]

You can find the capital letters which are NOT after a comma "," (in other words, beginning of a name) with this kind of If-statement:

For i = 2 To Len(str)
If Mid(str, i, 1) = UCase(Mid(str, i, 1)) And (Mid(str, i, 1) <> ",") And Mid(str, i - 1, 1) <> "," Then

I started i = 2 because otherwise (i-1) would give me errors.
This should get you started.

EDIT: Oh, in this case str being your cell.value as string.

 

[Peter_SSs]

For a manual option, see if this helps.

1. Select the column by clicking its heading label.
2. Data ribbon tab|Text to Columns|Delimited|Next|Choose only 'Other:' & with your cursor in the box to the right, type on your keyboard Ctrl+J (nothing will appear in the dialog box but continue anyway)|Finish

 

[Rick Rothstein]

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.

It is not entirely clear to me what you are attempting to do. Did you want to separate the names into separate cells? If so, did you want each cell's names listed across from there? Or did you want to create a long column of cells each containing a name? Or did you simply want to remove the individual line feeds and create a single (say) semi-colon delimited single line list within each cell? Or maybe something else entirely?

 

[hemmi1]

Rick: Ideally, I'd like to have each name Lastname, Firstname combination in a single cell. So I'd have a single cell reading Frost, Robert or Farmer, George, etc., and each of these cells would be in the same column. This way I could do a vlookup of the values in this column from a different report that has the instructors' names as last name, first name. The file I'm having trouble with is a list of future training events, and I need to cross reference each instructor to a list of instructors who are flagged as having an incorrect status, etc. This "instructor status" report is formatted nicely with the lastname, firstname in each cell in a column. Thanks for looking at this.

 

[hemmi1]

Thanks, Runsk -- I will try this, and let you know how it turns out

 

[hemmi1]

WOW. This works perfect! I had tried Text To Columns, but not the way you prescribed (CTL+J). Thanks so very much, I hope I can help someone out when they're confronted with this -Thanks, Peter

 

[Rick Rothstein]

Rick: Ideally, I'd like to have each name Lastname, Firstname combination in a single cell. So I'd have a single cell reading Frost, Robert or Farmer, George, etc., and each of these cells would be in the same column.

Give this macro a try...
[table="width: 500"]
[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][/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.

 

[hemmi1]

Thanks very much --- this looks like a comprehensive solution, and a great tool to add to my workbench I'll let you know how it pans out.

 

[hemmi1]

Rick: This indeed is a complete solution! I've been able to populate my lookup table with one keystroke
THanks very much, Rick.