Converting A String to a Column of Data

[carl]

I have some strings like this (some are as long as 4000 separated values):

A;AA;AAI;AAP;AAPL

I am looking for a way to convert these strings into column data like this:

A
AA
AAI
AAP
AAPL


Thank you in advance.

 

[Luke M]

Data - Text to Columns, delimited by semicolon.
Select Data, copy, paste special - transpose.

 

[carl]

Thanks. I should have provided a better description of my issue.

The strings that I work with are larger than 256 column restriction - some
of the strings have 3000 items.

Any ideas how to handle large strings ?

 

[Ron Rosenfeld]

I have some strings like this (some are as long as 4000 separated values):

A;AA;AAI;AAP;AAPL

I am looking for a way to convert these strings into column data like this:

A
AA
AAI
AAP
AAPL


Thank you in advance.

Without using VBA:

with your long string in A1:

First substring: =LEFT(A1,FIND(";",A1)-1)
Second substring:

=MID(A$1 & ";",FIND(CHAR(1),SUBSTITUTE(SUBSTITUTE(
A$1 & ";",";",CHAR(1),ROWS($1:1)),";",CHAR(2),ROWS($1:1)))+1,
FIND(CHAR(2),SUBSTITUTE(SUBSTITUTE(
A$1 & ";",";",CHAR(1),ROWS($1:1)),";",CHAR(2),ROWS($1:1)))-
FIND(CHAR(1),SUBSTITUTE(SUBSTITUTE(A$1 & ";",";",CHAR(1),
ROWS($1:1)),";",CHAR(2),ROWS($1:1)))-1)

and fill down until the formula starts returning #VALUE! errors (i.e. 4000+
rows.

Using VBA:

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first select the cell you wish to parse. Then
<alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

=============================================
Option Explicit
Sub SplitOnSemiColon()
Dim c As Range
Dim Temp As Variant
Dim i As Long
Set c = Selection
Temp = Split(c, ";")
c.Offset(1, 0).Resize(rowsize:=Cells.Rows.Count - c.Row).ClearContents
For i = 0 To UBound(Temp)
c.Offset(i + 1, 0).Value = Temp(i)
Next i
End Sub
============================
--ron