Sorting

Copper Contributor

I have a long excel worksheet with several columns. One of the columns lists different titles of employees at the upper levels found in a public university with a corresponding code. For example,

column c has the word Chancellor (3), Vice President (9), president (1), enrollment V.P. (46), etc.

My supervisor wants me to sort column C showing the codes in an ending orders with their corresponding title. So the first code is President (1), Chancellor (3), etc.

 

how would I do this?

 

6 Replies
Typo-supervisor wants column C to show the numbers and matching data in ascending order

@Debbo1984 

=SORTBY(C1:C18,NUMBERVALUE(MID(C1:C18,FIND("(",C1:C18)+1,FIND(")",C1:C18)-FIND("(",C1:C18)-1)))

With Office 365 or Excel for the web you can try this formula.

sorting.JPG 

Hi,

 

This

=SORTBY(C1:C18,MID(C1:C18,FIND("(",C1:C18),9)*-1)

 

9 so it's for numbers <= 9 999 999

replace 9 by 99 for more

 

@OliverScheurich 

Another option might be to create a custom list and then sort by the custom list.
https://support.microsoft.com/en-us/office/sort-data-using-a-custom-list-cba3d67a-c5cb-406f-9b14-a02...

@Hecatonchire 

Thank you for your formula which works perfectly however i don't understand why the *-1 works.

 

Another alternative which works in my sheet is this formula.

=SORTBY(C1:C18,NUMBERVALUE(TEXTAFTER(TEXTBEFORE(C1:C18,")"),"(")))

 

 

For my Excel (5) is negative

 

"(5)" * 1 = -5

 

So all the value of the MID are converted  to negative with *1 so i use *-1 to reverse the inversion.

 

@OliverScheurich