How to break text into individual cells based on separators

[ZIXILPLIX]

I have the following text in a spread sheet:
In A1 I have: asdfasdf//asdfasdfsadf//asdfasdfasdfasdfasdf//asdf//a//asdfasdfasdfasdfasdf//d//
in A2 I have: asdf//asdfasdf//asdfasdfasdf//asdfasdfasdfasdfasdf//a//asdf//asdfasdfasdf//d//
I have a couple hundred rows of text that follow the same format.

I'd like to have a macro that would break the text up into separate cells based on where the double forward-slashes are. So for Row 1, I'd like to see in B1 asdfasdf then in C1 asdfasdfsadf then in D1 asdfasdfasdfasdfasdf, etc. Or it could all happen on Sheet2 if it's cleaner. Then have the same thing happen in Row 2.

Any help would be greatly appreciated.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG19Jul17
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Sp [COLOR="Navy"]As[/COLOR] Variant, Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Sp = Split(Dn.Value, "//")
    Dn.Offset(, 1).Resize(, UBound(Sp)).Value = Sp
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[ZIXILPLIX]

Try this:-

[COLOR=navy]Sub[/COLOR] MG19Jul17
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, Sp [COLOR=navy]As[/COLOR] Variant, Temp [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    Sp = Split(Dn.Value, "//")
    Dn.Offset(, 1).Resize(, UBound(Sp)).Value = Sp
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Brilliant! Thank you!

 

[MickG]

You're welcome