Email address question

[slyporter]

I have an excel sheet with multiple email addresses in each cell in one column. Some cells have no addresses, others have many, each cell is different. The email addresses are semicolon delimited, so it's easy to do Text to Columns to extract the individual email addresses into separate columns. However, I'd like to get all of the emails listed one by one in one column vs. spread out across multiple columns. Any help would be much appreciated.

Here's an example of the data for one column
cell 1: xxx@xx.com; xxy@xx.com; xxz@xx.com
cell 2: blank
cell 3: xxa@xx.com; xxb@xx.com
cell 4: xxc@xx.com; xxd@xx.com; xxe@xx.com; xxf@xx.com; xxg@xx.com

I'd like to end up with (all in one column):
xxx@xx.com
xxy@xx.com
xxz@xx.com
xxa@xx.com
xxb@xx.com
xxc@xx.com
xxd@xx.com
xxe@xx.com
xxf@xx.com
xxg@xx.com

Thanks!
-Scott

 

[Momentman]

Here's the data, raw and edited(in colummn B)
<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">xxx@xx.com; xxy@xx.com; xxz@xx.com</td><td style=";">xxx@xx.com</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style=";">xxy@xx.com</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">xxa@xx.com; xxb@xx.com</td><td style=";">xxz@xx.com</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">xxc@xx.com; xxd@xx.com; xxe@xx.com; xxf@xx.com; xxg@xx.com</td><td style=";">xxa@xx.com</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">trial</td><td style=";">xxb@xx.com</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">xxc@xx.com; xxd@xx.com; xxe@xx.com; xxf@xx.com; xxg@xx.com</td><td style=";">xxc@xx.com</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style=";">xxd@xx.com</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style=";">xxe@xx.com</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style=";">xxf@xx.com</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style=";">xxg@xx.com</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style=";">xxc@xx.com</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style=";">xxd@xx.com</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style=";">xxe@xx.com</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style=";">xxf@xx.com</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style=";">xxg@xx.com</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet28</p><br /><br />

and here is the code

Sub SplitData()
    Dim I As Integer
    Dim j As Integer
    Dim ask
    Dim k As Integer
    
    k = 1
    For I = 1 To Range("A" & Rows.Count).End(xlUp).Row
        If Cells(I, 1).Value = "" Or Not InStr(1, Cells(I, 1).Value, "@") > 0 Then
            GoTo skiplab
        Else
            ask = Split(Cells(I, 1).Value, ";")
            For j = LBound(ask) To UBound(ask)
                Range("B" & k).Value = Trim(ask(j))
                k = k + 1
            Next j
        End If
skiplab:
    Next I
End Sub