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

[mightythor]

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.

[mightythor]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Let me know if the new title is sufficiently specific.

[Palmetto]

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

[stanleydgromjr]

mightythor,

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

[oldchippy]

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),">",""))

[mikerickson]

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

Please Login or Register  to view this content.

[mightythor]

Thank you all for your suggestions and help so far.

I guarantee that I will try to get one or more of these working Monday when I'm back at the work computer.

Thanks again so much.

[royUK]

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