Split text to multiple cells
 

I have a list (excerpt shown below) that, when copied and pasted from another
document, showed up in single cells instead of multiple columns. I need to
split the information into 4 columns: The CUSIP (number), symbol (ex:TXFCM),
the description, and the last text (ex:1Yes). My problem is that there are a
different number of spaces when I try to use mid(), left(), or right() to
count spaces. I have functions that work for 3 parts (just not the
description--long part):

*CUSIP* =LEFT(A11, SEARCH(" ",A11,1))

*symbol* =MID(A11,SEARCH(" ",A11,1)+1,SEARCH(" ",A11,SEARCH("
",A11,1)+1)-SEARCH(" ",A11,1))

*last text (3 or 4 characters)* =TRIM(RIGHT(A11,4))

Please help me with the code to pull the description out of the middle.
There are different numbers of words each time, so I'm hoping there's a way
to pull the text after 2 spaces up until the first space from the right.
Please advise if this is possible and how it would be done. THANKS!

876935800 TXFCM AIM TFIT CASH RESERVE PORTFOLIO CASH MANAGEMENT CLS M/M 1Yes
01748V866 ARGXX ALLEGIANT GOVERNMENT MONEY MKT FD CL A M/M 3Yes
01748V841 ARRXX ALLEGIANT MONEY MARKET FD CLASS A M/M 3Yes

Split text to multiple cells
 

One way:

Data=Text to Columns

Fixed width

Separate after CUSIP/symbol/remainder of string

This will move data into columns A, B, C

In D1:

=left(C1,len(C1)-4) to leave description

inE1:

=right(C1,4) to leave "1YES"

Copy these down

then copy /past special - values columns D & E (to themselves)

Delete column C

HTH





"Lauren H" wrote:

I have a list (excerpt shown below) that, when copied and pasted from another
document, showed up in single cells instead of multiple columns. I need to
split the information into 4 columns: The CUSIP (number), symbol (ex:TXFCM),
the description, and the last text (ex:1Yes). My problem is that there are a
different number of spaces when I try to use mid(), left(), or right() to
count spaces. I have functions that work for 3 parts (just not the
description--long part):

*CUSIP* =LEFT(A11, SEARCH(" ",A11,1))

*symbol* =MID(A11,SEARCH(" ",A11,1)+1,SEARCH(" ",A11,SEARCH("
",A11,1)+1)-SEARCH(" ",A11,1))

*last text (3 or 4 characters)* =TRIM(RIGHT(A11,4))

Please help me with the code to pull the description out of the middle.
There are different numbers of words each time, so I'm hoping there's a way
to pull the text after 2 spaces up until the first space from the right.
Please advise if this is possible and how it would be done. THANKS!

876935800 TXFCM AIM TFIT CASH RESERVE PORTFOLIO CASH MANAGEMENT CLS M/M 1Yes
01748V866 ARGXX ALLEGIANT GOVERNMENT MONEY MKT FD CL A M/M 3Yes
01748V841 ARRXX ALLEGIANT MONEY MARKET FD CLASS A M/M 3Yes

Split text to multiple cells
 

If CUSIP & sysmbol are fixed length:

CUSIP

=LEFT(A5,9)

Symbol

=MID(A5,11,5)

Description

=MID(A5,17,LEN(A5)-21)

Last text

=RIGHT(A5,4)


"Lauren H" wrote:

I have a list (excerpt shown below) that, when copied and pasted from another
document, showed up in single cells instead of multiple columns. I need to
split the information into 4 columns: The CUSIP (number), symbol (ex:TXFCM),
the description, and the last text (ex:1Yes). My problem is that there are a
different number of spaces when I try to use mid(), left(), or right() to
count spaces. I have functions that work for 3 parts (just not the
description--long part):

*CUSIP* =LEFT(A11, SEARCH(" ",A11,1))

*symbol* =MID(A11,SEARCH(" ",A11,1)+1,SEARCH(" ",A11,SEARCH("
",A11,1)+1)-SEARCH(" ",A11,1))

*last text (3 or 4 characters)* =TRIM(RIGHT(A11,4))

Please help me with the code to pull the description out of the middle.
There are different numbers of words each time, so I'm hoping there's a way
to pull the text after 2 spaces up until the first space from the right.
Please advise if this is possible and how it would be done. THANKS!

876935800 TXFCM AIM TFIT CASH RESERVE PORTFOLIO CASH MANAGEMENT CLS M/M 1Yes
01748V866 ARGXX ALLEGIANT GOVERNMENT MONEY MKT FD CL A M/M 3Yes
01748V841 ARRXX ALLEGIANT MONEY MARKET FD CLASS A M/M 3Yes

Split text to multiple cells
 

Thanks! Worked like a charm!



"Toppers" wrote:

One way:

Data=Text to Columns

Fixed width

Separate after CUSIP/symbol/remainder of string

This will move data into columns A, B, C

In D1:

=left(C1,len(C1)-4) to leave description

inE1:

=right(C1,4) to leave "1YES"

Copy these down

then copy /past special - values columns D & E (to themselves)

Delete column C

HTH





"Lauren H" wrote:

I have a list (excerpt shown below) that, when copied and pasted from another
document, showed up in single cells instead of multiple columns. I need to
split the information into 4 columns: The CUSIP (number), symbol (ex:TXFCM),
the description, and the last text (ex:1Yes). My problem is that there are a
different number of spaces when I try to use mid(), left(), or right() to
count spaces. I have functions that work for 3 parts (just not the
description--long part):

*CUSIP* =LEFT(A11, SEARCH(" ",A11,1))

*symbol* =MID(A11,SEARCH(" ",A11,1)+1,SEARCH(" ",A11,SEARCH("
",A11,1)+1)-SEARCH(" ",A11,1))

*last text (3 or 4 characters)* =TRIM(RIGHT(A11,4))

Please help me with the code to pull the description out of the middle.
There are different numbers of words each time, so I'm hoping there's a way
to pull the text after 2 spaces up until the first space from the right.
Please advise if this is possible and how it would be done. THANKS!

876935800 TXFCM AIM TFIT CASH RESERVE PORTFOLIO CASH MANAGEMENT CLS M/M 1Yes
01748V866 ARGXX ALLEGIANT GOVERNMENT MONEY MKT FD CL A M/M 3Yes
01748V841 ARRXX ALLEGIANT MONEY MARKET FD CLASS A M/M 3Yes

Split text to multiple cells
 

If your description ***always*** has a 3 of 4 character ending (never less
than 3 or more than 4), put this formula in a blank cell next to the first
row of descriptions and copy down...

=TRIM(MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,LEN(A1)-FIND(" ",A1,FIND("
",A1)+1)-4))

Rick


"Lauren H" wrote in message
...
I have a list (excerpt shown below) that, when copied and pasted from
another
document, showed up in single cells instead of multiple columns. I need
to
split the information into 4 columns: The CUSIP (number), symbol
(ex:TXFCM),
the description, and the last text (ex:1Yes). My problem is that there
are a
different number of spaces when I try to use mid(), left(), or right() to
count spaces. I have functions that work for 3 parts (just not the
description--long part):

*CUSIP* =LEFT(A11, SEARCH(" ",A11,1))

*symbol* =MID(A11,SEARCH(" ",A11,1)+1,SEARCH(" ",A11,SEARCH("
",A11,1)+1)-SEARCH(" ",A11,1))

*last text (3 or 4 characters)* =TRIM(RIGHT(A11,4))

Please help me with the code to pull the description out of the middle.
There are different numbers of words each time, so I'm hoping there's a
way
to pull the text after 2 spaces up until the first space from the right.
Please advise if this is possible and how it would be done. THANKS!

876935800 TXFCM AIM TFIT CASH RESERVE PORTFOLIO CASH MANAGEMENT CLS M/M
1Yes
01748V866 ARGXX ALLEGIANT GOVERNMENT MONEY MKT FD CL A M/M 3Yes
01748V841 ARRXX ALLEGIANT MONEY MARKET FD CLASS A M/M 3Yes