Combine multiple columns into a single column

[Punchin83]

I have looked all over and short of manually highlighting and copy+pasting each column, I can't find a way to do this.

I have a list of names where some cells have multiple names per cell separated by a semicolon. I know I can use Text to Column to spread these out, but what I'm wanting is only one name per cell and any additional to be added to the end of the column in their own cells. This will be a table as well.

Everything I can find is either wanting me to use Text to Columns thinking I want the opposite of what I'm asking, or Concatenate, also not what I'm wanting.

 

[iggydarsa]

can you provide a sample data of what you have and what you need?

 

[Punchin83]

From this:
James; John; Sally
Diane
Elizabeth; Markus

To this:
James
Diane
Elizabeth
John
Markus
Sally

Order of the names doesn't matter, I just need all the names in their own cells in the same table column.

 

[iggydarsa]

If you are comfortable with using VBA, it can easily be done with this code:

Sub SplitBySemicolonExample()
    'Define variables
    Dim MyArray() As String, MyString As String, I As Variant, N As Integer, Counter As Long, R As Long, LR As Long
    Counter = 1
    
    'Find the last Row where the names are (assuming they are on Column A)
    LR = Range("A" & Rows.Count).End(xlUp).Row
    
    'Loop through all the rows that has the names
    For R = 1 To LR
        'Sample string with semi colon delimiters
        MyString = Range("A" & R).Value
        
        'Use Split function to divide up the component parts of the string
        MyArray = Split(MyString, ";")
        
        'iterate through the array
        For N = 0 To UBound(MyArray)
        'Place each name into the column B of the worksheet
            Range("B" & Counter).Value = Trim(MyArray(N))
            Counter = Counter + 1
        Next N
    Next R
End Sub

If you dont know how to use VBA please let me know so I can try to show you how to paste this code and execute.

Cheers.