Split text to multiple cells

[Lauren H]

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

 

[Toppers]

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

 

[Toppers]

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]

Thanks! Worked like a charm!

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

 

[Rick Rothstein \(MVP - VB\)]

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