Excel rows to columns question

[LennyD57]

I have been sent a worksheet which has multiple names and email addresses in this format :- Duck,Donald<donald.duck@disney.com>;Mouse,Micky<micky.mouse@disney.com>;Mouse,Minnie<minnie.mouse@disney.com;

These are all in cell A1!

I need to split all of these down into 2 columns, split between Names and email addresses.

Any help will be much appreciated. 

[#2]

@LennyD57 

The Text to Columns may help here.  Assuming the ; is your delimiter.

1. Select the cell or column that contains the text you want to split.
2. Select Data > Text to Columns.
3. In the Convert Text to Columns Wizard, select Delimited > Next.
4. Select the Delimiters for your data. ...
5. Select Next.

Please give it a go and let us know how this works.

[#3]

@LennyD57 

Try this macro:

Sub SplitData()
    Dim r As Long
    Dim m As Long
    Dim c As Long
    Dim persons() As String
    Dim parts() As String
    Application.ScreenUpdating = False
    m = Cells(Rows.Count, 1).End(xlUp).Row
    For r = 1 To m
        persons = Split(Cells(r, 1).Value, ";")
        For c = 0 To UBound(persons)
            If persons(c) <> "" Then
                parts = Split(persons(c), "<")
                Cells(r, 2 * c + 2).Value = parts(0)
                Cells(r, 2 * c + 3).Value = Replace(parts(1), ">", "")
            End If
        Next c
    Next r
    ActiveSheet.UsedRange.Offset(0, 1).EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub

[#4]

@LennyD57 

You may also use Power Query to transform the data into the desired format.

If you convert your raw data into an Excel Table and rename the table as Raw_Data, you may add a blank query with the following M Code and then load the data back into the worksheet.

let
    Source = Excel.CurrentWorkbook(){[Name="Raw_Data"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1", Splitter.SplitTextByDelimiter("<", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter1",">","",Replacer.ReplaceText,{"Column1.2"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([Column1.2] <> null)),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1.1", "Name"}, {"Column1.2", "Email"}})
in
    #"Renamed Columns"

Please find the attached for more details.

You may replace the data of the blue table under the header Column1 with your data and then click inside the green table and choose Refresh to update the output returned by the query based on new data if you want to continue with the same file.

[#5]

@LennyD57 

1. Select cell a1. Use menu option Data > Text to Columns > Delimited > Semicolon
2. Select all occupied cells in row 1. Copy then right click on destination cell and select Paste Special using option Transpose
3. Select all pasted values and use menu option Data > Text to Columns > Delimited > Other "<"
4. The email column will have a trailing ">" which you can remove using several different methods like:
   =SUBSTITUTE(B7,">","")
   =LEFT(B8,LEN(B8)-1)
   Or even text to columns using delimiter ">"

See attached screenshots