Sort by text only within cell.

New Contributor

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?

4 Replies

@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.

@Skypilot 

That is like

image.png

with

=SORTBY(B3:B5,RIGHT(B3:B5,LEN(B3:B5)-SEARCH("-",B3:B5)))
Thank You!
Thank You!