Prev: How can stop a 9 digit number from formatting scientific
Next: circular formula gives different values
From: carl on 17 May 2010 08:25
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.
From: Luke M on 17 May 2010 08:48
Data - Text to Columns, delimited by semicolon.
Select Data, copy, paste special - transpose.

--
Best Regards,

Luke M
"carl" <carl(a)discussions.microsoft.com> wrote in message
news:E828806F-40A2-4C85-8D01-D55BD8ED28BB(a)microsoft.com...
>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.


From: carl on 19 May 2010 13:21
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 ?

"Luke M" wrote:

> Data - Text to Columns, delimited by semicolon.
> Select Data, copy, paste special - transpose.
>
> --
> Best Regards,
>
> Luke M
> "carl" <carl(a)discussions.microsoft.com> wrote in message
> news:E828806F-40A2-4C85-8D01-D55BD8ED28BB(a)microsoft.com...
> >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.
>
>
> .
>
From: Ron Rosenfeld on 19 May 2010 15:34
On Mon, 17 May 2010 05:25:01 -0700, carl <carl(a)discussions.microsoft.com>
wrote:

>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
 | 
Pages: 1
Prev: How can stop a 9 digit number from formatting scientific
Next: circular formula gives different values