I have a string like this in A2

IWM;QQQQ;SPY

Is there a way to have this string transposed in A3,B3,C3 like this:

IWM
QQQQ
SPY

Thank you in advance.

On May 30, 6:23 pm, carl wrote:
I have a string like this in A2

IWM;QQQQ;SPY

Is there a way to have this string transposed in A3,B3,C3 like this:

IWM
QQQQ
SPY

Thank you in advance.

In A3:
=LEFT(A2,FIND(";",A2)-1)
In A4: This is an *array* formula and you can copy it down.
=MID($A$2,SUM(LEN($A$3:A3))+ROWS($A$3:A3)+1,FIND(" ;",$A$2,SUM(LEN($A
$3:A3)+ROWS($A$3:A3))-SUM(LEN($A$3:A3)-ROWS($A$3:A3)+1)))
An *array* formula must be committed with Shift+Ctrl+Enter.

HTH
Kostis Vezerides

You can quickly do a Data-Text to Columns-Delimited and use a semicolon as
the delimiter. That will split the original string into 3 cells that you can
subsequently move

If you want a formulaic approach, use these formulas

A3: =LEFT(A2,SEARCH(";",A2,1)-1)
B3: =LEFT(SUBSTITUTE(A2,A3&";",""),SEARCH(";",SUBSTITU TE(A2,A3&";",""),1)-1)
C3: =SUBSTITUTE(A2,A3&";"&B3&";","",1)

"carl" wrote:

I have a string like this in A2

IWM;QQQQ;SPY

Is there a way to have this string transposed in A3,B3,C3 like this:

IWM
QQQQ
SPY

Thank you in advance.

I have a string like this in A2

IWM;QQQQ;SPY

Is there a way to have this string transposed in A3,B3,C3 like this:

IWM
QQQQ
SPY

This seems to work...

In A3: =LEFT(A2,FIND(";",A2)-1)

In B3: =MID(A2,LEN(A3)+2,LEN(A2)-LEN(A3)-LEN(C3)-2)

In C3: =RIGHT(A2,FIND(";",A2)-1)

Rick

One way, make sure you have plenty of room to the right with empty columns,
select the cell and do datatext to columns, select delimited, click next
then select semicolon, now you will have each string in a separate column,
now select all words and copy them, select a cell where you want to paste
them and then do editpaste special and select transpose



--
Regards,

Peo Sjoblom


"carl" wrote in message
...
I have a string like this in A2

IWM;QQQQ;SPY

Is there a way to have this string transposed in A3,B3,C3 like this:

IWM
QQQQ
SPY

Thank you in advance.

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.

Regards.

"Duke Carey" wrote:

You can quickly do a Data-Text to Columns-Delimited and use a semicolon as
the delimiter. That will split the original string into 3 cells that you can
subsequently move

If you want a formulaic approach, use these formulas

A3: =LEFT(A2,SEARCH(";",A2,1)-1)
B3: =LEFT(SUBSTITUTE(A2,A3&";",""),SEARCH(";",SUBSTITU TE(A2,A3&";",""),1)-1)
C3: =SUBSTITUTE(A2,A3&";"&B3&";","",1)

"carl" wrote:

I have a string like this in A2

IWM;QQQQ;SPY

Is there a way to have this string transposed in A3,B3,C3 like this:

IWM
QQQQ
SPY

Thank you in advance.

Data/ text to columns will work whether it is 3 elements or 100.
Do that, then copy, and paste special/ transpose.
--
David Biddulph

"carl" wrote in message
...
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.

Regards.

"Duke Carey" wrote:

You can quickly do a Data-Text to Columns-Delimited and use a semicolon
as
the delimiter. That will split the original string into 3 cells that you
can
subsequently move
....

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.

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.

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.

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

I'm not sure I see why... the code appeared to run fine... can you clarify
this for me? Well, when I say the code runs fine, I did see an error
produced when I did something elsewhere on the sheet that generated its own
error; however, adding On Error Resume Next seems to take care of that
problem. Is that not a desirable thing to add to a Worksheet Change event?

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

I got the impression that the OP wanted an automatic solution rather than
one that required manual activation.

Rick