Can I get Excel to interpret certain characters as automatic new cell or new row?

First off, I'm an excel noob, so if I'm in the wrong forum, please redirect me.

What I want to do goes something like this:

- On a blank spreadsheet, I want to paste the following text into cell A1:

Bob Smith <bobsmith@gmail.com>, John Doe <johndoe@hotmail.com>, Jane Doe <janedoe@att.net>, ...

- So that it automatically fills in the spreadsheet like so:

A1: Bob Smith - B1: bobsmith@gmail.com
A2: John Doe - B2: johndoe@hotmail.com
A3: Jane Doe - B3: janedoe@att.net
...

Now, I know that, one at a time, I can paste each entry into its own cell in the first column and then go back and do the automatic column separation with the "<" character, but that won't help me nearly as much as what I want to achieve as described above.

Any help in this regard will be GREATLY appreciated. This is for a big huge work project.

After pasting the data in the cell, record a macro doing the following steps

• Select the cell with the data
• Menu > Data > Text to Columns (choose comma separated option)
• Select all of the cells with now separated values
• Right-click > Copy
• select another cell (empty column), right-click > Paste Special > Transpose

Perhaps, if this does what you want, it might be moved to the Change event.

Sub test()
    Dim myRRay As Variant
    
    With Range("A1")
        myRRay = Split(Application.Substitute(Application.Substitute(CStr(.Value), " <", "<"), "<", Chr(5) & "<"), ",")
        .Resize(UBound(myRRay), 1).EntireRow.Insert shift:=xlDown
    End With
    With Range("A1").Resize(UBound(myRRay) + 1, 1)
         .Value = Application.Transpose(myRRay)
         Application.DisplayAlerts = False
         
         .TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:=Chr(5), FieldInfo:=Array(Array(1, 1), Array(2, 1))
        
        Application.DisplayAlerts = True
        .Cells(1, 1).EntireRow.Insert
    End With
End Sub

mightythor,

Detach/open the attached workbook "Split - Can I do this - mightythor - SDG11.xls", and run macro "mightythor".

Hi,

If you have your data in row 1, go to Data > Text-to-columns and use the comma for the delimiter to split it so that the name and email address is in one cell. Then select all the cells in row 1, copy , select Sheet2 cell A1 and Paste Special > Transpose.

You should now have all the names and email address in column A of Sheet2, like so:-

Bob Smith <bobsmith@gmail.com>
John Doe <johndoe@hotmail.com>
Jane Doe <janedoe@att.net>

Now to split these into separate cells, put this in B1 and copy downwards by double-clicking the little black square on the bottom right of B1

=TRIM(LEFT(A1,FIND("<",A1)-1))

and this in C1 and copy downwards as before

=HYPERLINK(SUBSTITUTE(MID(A1,FIND("<",A1)+1,50),">",""))

The Thread Title still does not comply with the Rules, ie it contains unnecessary wording. The Title should be along the lines of a search engine string