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

For example, ; ; ;...

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:
;
;
;
..
..
..

Any ideas would be appreciated.

Sincerely,
Jim Berglund

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?

Jim Berglund wrote:

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

For example, ; ; ;...

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:
;
;
;
.
.
.

Any ideas would be appreciated.

Sincerely,
Jim Berglund

--

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 wrote:

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

For example, ; ; ;...

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:
;
;
;
.
.
.

Any ideas would be appreciated.

Sincerely,
Jim Berglund

--

Dave Peterson

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

"Dave Peterson" wrote in message
...
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 wrote:

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

For example, ; ; ;...

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:
;
;
;
.
.
.

Any ideas would be appreciated.

Sincerely,
Jim Berglund

--

Dave Peterson