Excel help - Seperating out delimited strings

[Domo]

I have an Excel spreadsheet with 3 columns.
Company Name, Company Number, Email Address,

The Email address column contains multiple addresses per row seperated by a semicolon. What I need is one row per email address.

I've meddled with Text to Columns but this isn't the desired result. As an example I neeed to get this..

<Row1> ABC Inc, .234, [email protected];[email protected]

into this..

<Row1> ABC Inc, 1234, [email protected]
<Row2> ABC Inc, 1234, [email protected]

 

[PMKeates]

Text to Columns and then some VBA.

 

[Domo]

Yeah I get the Text to Columns bit but its the VB I'm struggling with (not being a VB developer!).
Any help would be greatly appreciated

I've scanned Google results but not found anything which retains all other columns when copying.

 

[PMKeates]

Ermm, I'm OK with VBA. Let me see what I can do!

 

[fretted]

Try this:

Const STARTFROM = 1

Sub DoFunkyStuff()
Dim i, j, k As Integer
Dim EmailAddresses As Variant

k = 1
For i = STARTFROM To Range("A65536").End(xlUp).Row
EmailAddresses = Split(Range("c" & i), ";")
For j = 0 To UBound(EmailAddresses)
Debug.Print EmailAddresses(j)
Range("sheet2!" & "a" & k).Value = Range("a" & i).Value
Range("sheet2!" & "b" & k).Value = Range("b" & i).Value
Range("sheet2!" & "c" & k).Value = EmailAddresses(j)
k = k + 1
Next j
Next i
End Sub

 

[PMKeates]

Yeah that works, and is neater than my attempt

I didn't think about just printing it to another sheet. I tried to do it in place so had to figure out all the offsets

 

[Domo]

Good job fretted - genuine thanks for getting me out of an almost impossible situation!!

Thanks to you too PMKeates for your advice.

 

[Makhaira]

You could also have used text to columns, then copied the email column, and run that through text to columns again, but this time using a semicolon as the delimiter.