Sort by text only within cell.

%3CLINGO-SUB%20id%3D%22lingo-sub-1259607%22%20slang%3D%22en-US%22%3ESort%20by%20text%20only%20within%20cell.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1259607%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20single%20column%20with%20over%20200%20rows%20of%20data.%20Using%20sort%2C%20it%20always%20keys%20on%20the%20first%20character%20set%20which%20can%20be%20alphanumeric%2C%20in%20this%20case%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3E%3CSTRONG%3E1%3C%2FSTRONG%3E968W-RED%20JACKET%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E%3CSTRONG%3E3%3C%2FSTRONG%3E00J-PURPLE%20CAP%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E%3CSTRONG%3E6%3C%2FSTRONG%3EC790-BLUE%20SHOES%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EWhat%20I%20would%20like%20to%20sort%20by%20is%20the%20right%20grouping%20by%20alpha%20only%22%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3E6C790-%3CSTRONG%3EB%3C%2FSTRONG%3ELUE%20SHOES%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E300J-%3CSTRONG%3EP%3C%2FSTRONG%3EURPLE%20CAP%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E1968W-%3CSTRONG%3ER%3C%2FSTRONG%3EED%20JACKET%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20this%20be%20done%20and%20how%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1259607%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1259970%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%20by%20text%20only%20within%20cell.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1259970%22%20slang%3D%22en-US%22%3ETo%20the%20best%20of%20knowledge%2C%20it%20is%20not%20possible%20with%20SORT%20dynamic%20array%20function.%3C%2FLINGO-BODY%3E
Highlighted
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
Highlighted

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

Highlighted

@Skypilot 

That is like

image.png

with

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