MS Excel Question

Has that stupid paperclip ever helped anybody?

 

Just reread the OP a little better. Here's a very quick macro that will take a target range and put them in a column that you specify. Just fill in the opening fields to fit your worksheet.

Code:

Sub ConcatenateNames()
    Dim ls_Worksheet As String
    Dim ls_SourceColumn As String
    Dim li_StartRow As Integer
    Dim li_EndRow As Integer
    Dim ls_TargetColumn As String
    Dim li_TargetRow As Integer
    Dim li_Index As Integer
    Dim lb_Continue As Boolean
    Dim li_Result As Integer
    Dim ls_Range As String
    Dim ls_TargetRange As String
    Dim ls_Temp As String
    Dim ls_Name As String

    '****************************************
    'Fill in these fields
    'The values below, take all names in Column B, rows 4-5
    'on the worksheet "Sheet1".  It will list the separate 
    'names in D5 in the same worksheet.    
    '****************************************
    ls_Worksheet = "Sheet1"
    ls_SourceColumn = "B"
    li_StartRow = 4
    li_EndRow = 5
    ls_TargetColumn = "D"
    li_TargetRow = 5
    
    li_Index = li_StartRow
    Do While (li_Index <= li_EndRow)
        lb_Continue = False
        ls_Range = ls_SourceColumn + CStr(li_Index)
        ls_Temp = Sheets(ls_Worksheet).Range(ls_Range).Value
 
        li_Result = InStr(1, ls_Temp, Chr(10))
        
        If (li_Result > 0) Then
            lb_Continue = True
        End If
        
        Do While (lb_Continue = True)
            ls_Name = Mid(ls_Temp, 1, li_Result - 1)
            ls_TargetRange = ls_TargetColumn + CStr(li_TargetRow)
            Sheets(ls_Worksheet).Range(ls_TargetRange).Value = ls_Name
            li_TargetRow = li_TargetRow + 1
            ls_Temp = Trim(Mid(ls_Temp, li_Result + 1, 500))
            
            li_Result = InStr(1, ls_Temp, Chr(10))
            If (li_Result > 0) Then
                lb_Continue = True
            Else
                lb_Continue = False
            End If
            
        Loop
    
        ls_TargetRange = ls_TargetColumn + CStr(li_TargetRow)
        Sheets(ls_Worksheet).Range(ls_TargetRange).Value = ls_Temp
        li_TargetRow = li_TargetRow + 1
    
        li_Index = li_Index + 1
    Loop
End Sub

EDIT: I should have stated that it will take a source column of mixed names (separated by ALT+ENTER or on their own).

 

No worries. Let me know if you have any problems running it.