Thank you.

This may end up working for me.

I could not get the code to work though. I copied into a module but the
string in A1 did not get produced in row 2.

Than said, I really need the string to be produced in (transposed) in ColA.

Any thoughts ?

"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.