Transpose a String

C

carl

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

vezerid

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.
Click to expand...

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
 
D

Duke Carey

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(";",SUBSTITUTE(A2,A3&";",""),1)-1)
C3: =SUBSTITUTE(A2,A3&";"&B3&";","",1)
 
R

Rick Rothstein \(MVP - VB\)

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
Click to expand...

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
 
P

Peo Sjoblom

One way, make sure you have plenty of room to the right with empty columns,
select the cell and do data>text 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 edit>paste special and select transpose
 
C

carl

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

David Biddulph

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

carl said:
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.
Click to expand...
....
 
R

Rick Rothstein \(MVP - VB\)

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.
Click to expand...

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

Duke Carey

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
 
C

carl

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 ?
 
R

Rick Rothstein \(MVP - VB\)

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
Click to expand...

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.
Click to expand...

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

Rick
 
Top