New Contributor

Sorting

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

Re: Sorting

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

Re: Sorting

``=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.

Re: Sorting

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

Re: Sorting

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

Re: Sorting

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,")"),"(")))

Re: Sorting

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.