Delimiting a cell to the rows below

[buhay]

Does anyone one know how I can delimit serveral cells of ColC filled with several e-mails seperated by ";" and place each e-mail in the rows below?

For example before executing the macro


------------ColC----------

row1 ------E-Mails-------

row2-aa@aa.com;bb@bb.com;cc@cc.com

row3-dd@dd.com;ee@ee.com;ff@ff.com

row4-gg@gg.com;hh@hh.com

row5-ii@ii.com


Result after executing the macro


----------ColC----------

row1 -----E-Mails-------

row2-aa@aa.com

row3-bb@bb.com

row4-cc@cc.com

row5-dd@dd.com

row6-ee@ee.com

row7-ff@ff.com

row8-gg@gg.com

row9-hh@hh.com

row10-ii@ii.com

Any help is hightly appreciated

[Sean.DiSanti]

Check out the Split() function. breaks a delimited string into an array that you can then do whatever you want with

[buhay]

I don't even have the split function built in my excel 2003.

I've been trying to use the delimited method but I can only seperating the e-mails on the same row and not below the cell.

[Sean.DiSanti]

odd... it's in mine... it's standard VB i thought?

[Blade Hunter]

What happens when you run this:

[vba]
Sub TestSplit()
Dim MyVar As Variant
Dim X As Long
MyVar = Split("1;Hello;World;93", ";")
For X = LBound(MyVar) To UBound(MyVar)
MsgBox MyVar(X)
Next
End Sub
[/vba]

I thought Split existed from yonks ago.

[buhay]

there's a delimit function but no split
it must be the same right?

[Sean.DiSanti]

i don't have Delimit(), but i would think it would do the opposite, and concatenate a string based on a delimiter, rather than separating it..

[Bob Phillips]

What language version of Excel do you have? Are you talking about Excel rather than VBA?

[Sean.DiSanti]

he said he's using excel 2003, but i don't have a delimit spreadsheet function either?