Rick - Using Worksheet Change event effectively requires you to turn off the
events when your routine is going to change the worksheet

Application.EnableEvents = False
' do your stuff, then turn the events back on
Application.EnableEvents = turn


Here's a slight variation. Run it on demand, not automatically.

Public Sub SplitXpose()
Dim ar() As String
Dim str As String
Dim intAr As Integer

str = Selection.Text
ar() = Split(str, ";")
intAr = UBound(ar) + 1
Selection.Offset(1, 0).Resize(intAr, 1) = WorksheetFunction.Transpose(ar)

End Sub

"Rick Rothstein (MVP - VB)" wrote:

Thanks. I did not explain the problem too well.

My string could be 100 elements long. For example, A;B;C;EE;GGGG;....

I need a formula that can produce this:

A
B
C
EE
GGGG
etc.

Are you up for a macro? Use this Worksheet's Change event...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim Fields() As String
If Target = Range("a1") Then
Fields = Split(Range("a1").Value, ";")
Range("a2").Activate
For X = 0 To UBound(Fields)
ActiveCell.Value = Fields(X)
ActiveCell.Offset(0, 1).Activate
Next
End If
End Sub

Whenever you change the contents of A1, the semi-colon delimited text will
be split in row 2 from column A to whatever column is needed to house the
last delimited piece of text.

Rick

Note: I am newly returned to Excel after a long absence; so my code may look
'odd' and/or need tightening.