Sort

  • Thread starter Thread starter Brandon
  • Start date Start date
B

Brandon

I have a spreadsheet with about a thousand lines of stock sales for the year.
I need to sort by this column alphabetically by the first word after the word
"shares".

DESCRIPTION
10,430.000 SHARES UIT FIRST TRUST TA
10,430.000 SHARES UIT FIRST TRUST TA
32.801 SHARES FRANKLIN TEMPLETON
7.347 SHARES CAPITAL WORLD BOND FUND
3.000 SHARES OF UIT FIRST TRUST
8.448 SHARES LOOMIS SAYLES BOND FUND
37.616 SHARES FRANKLIN TEMPLETON
14.221 SHARES LORD ABBETT FUND
7.000 SHARES OF UIT FIRST TRUST
8.919 SHARES LOOMIS SAYLES BOND FUND
44.000 SHARES UIT FIRST TRUST TARGET
8.454 SHARES DAVIS NEW YORK VENTURE
4.146 SHARES FRANKLIN TEMPLETON


Any ideas how I could approach this?


TYIA,
Brandon
 
WORK WITH A COPY OF YOUR DATA

select all that data and press Ctrl-H
search for Shares_ [the underscore means a space]
replace with shares!
and click Replace All
Now, with all the data still selected (in a single column), press Alt-d and
e (for Data->Text to columns)
Choose delimited and the click Next
Click in the Other checkbox and type in the ! sign
Click Finish and you'll have 2 columns and you can sort on the second one
 
It looks like some of the records contain 'SHARES' and some contain 'SHARES
OF'. In an empty column, enter this formula on the first row of data and
adjust the A1 references as necessary:

=IF(ISNUMBER(FIND("SHARES OF ",A1)),RIGHT(A1,LEN(A1)-FIND("SHARES OF
",A1)-9),RIGHT(A1,LEN(A1)-FIND("SHARES ",A1)-6))

Copy the formula down through all rows of data. Then select all the data and
sort by the helper column with the above formula.

Hope this helps,

Hutch
 
Back
Top