Forum Discussion

Skypilot's avatar
Skypilot
Copper Contributor
Mar 27, 2020

Sort by text only within cell.

I have a single column with over 200 rows of data. Using sort, it always keys on the first character set which can be alphanumeric, in this case:

 

1968W-RED JACKET

300J-PURPLE CAP

6C790-BLUE SHOES

 

What I would like to sort by is the right grouping by alpha only"

 

6C790-BLUE SHOES

300J-PURPLE CAP

1968W-RED JACKET

 

Can this be done and how?

excel

4 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Mar 27, 2020

    Skypilot In case you are on the latest Excel (Office365) it can be done fairly easily with the following formula. Suppose you have your numerically sorted list in A1:A3. The following formula in B1 will sort it based on whatever comes after the "-":

     

    =LEFT(A1:A3,SEARCH("-",A1:A3))&SORT(MID(A1:A3,SEARCH("-",A1:A3)+1,LEN(A1:A3)-SEARCH("-",A1:A3)))

     

     See attached for a working example.

Resources