Splitting a row of email addresses

[Jim Berglund]

I have a cell containg a row of email addresses seperated with semicolons.

For example, (e-mail address removed); (e-mail address removed); (e-mail address removed);...

I'd like to spit them into a vertical list with a single address per cell
that I could use to create a distribution list, but I can't figure out how
to do it.

eg:
(e-mail address removed);
(e-mail address removed);
(e-mail address removed);
..
..
..

Any ideas would be appreciated.

Sincerely,
Jim Berglund

 

[Dave Peterson]

If the list had no more entries than the number of columns in the worksheet, you
could use:

Data|text to columns|delimited by semicolon
then edit|copy
then edit|paste special transpose.

Do you really want that trailing semicolon?

 

[Dave Peterson]

Or if you're using xl2k or higher, you could use something like this:

Option Explicit
Sub testme01()

Dim myStr As String
Dim mySplit As Variant

myStr = ActiveCell.Value
mySplit = Split(myStr, ";")

ActiveCell.Offset(0, 1).Resize(UBound(mySplit) _
- LBound(mySplit) + 1, 1).Value _
= Application.Transpose(mySplit)

End Sub

(Split was added in xl2k.)

 

[Jim Berglund]

Many thanks - it worked perfectly, and I researched out how it did it.
Pretty Clever!
Jim