VBA to break text in single cell into parts given a delimiter

[rsulliva]

I would like to run a macro that will break a single cell into many cells in a column of data. Example: Cell A1 contains the text "text1 & text2 & text3 & text4". Actually, there are hundreds of texts with "&" in between within this cell. I would like the macro to be able to break this text into B1:B4 as follows. B1: text1; B2: text2; B3: text3; B4: text4; etc.

How can this be done using a Macro? Thank you.

 

[Peter_SSs]

Do you really need a macro as this is readily achieved with a formula?

rsulliva.xlsm
	A	B
1	text1 & text2 & text3 & text4	text1
2		text2
3		text3
4		text4
5
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=TRIM(TEXTSPLIT(A1,,"&"))
Dynamic array formulas.

If you must have vba then you could use the same idea only with vba

Sub Break_Text()
  With Range("B1")
    .Formula2 = "=trim(textsplit(A1,, ""&""))"
    .SpillingToRange.Value = .SpillingToRange.Value
  End With
End Sub

 

[rsulliva]

Hey Peter_SSs, your solutions worked great. I didn't know the formula even existed. Great to know! Thanks

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.