Mar 22 2023 08:39 PM
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?
Mar 22 2023 09:03 PM
Mar 23 2023 01:57 AM
=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.
Mar 23 2023 07:08 AM
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
Mar 23 2023 07:13 AM
Mar 23 2023 01:07 PM
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,")"),"(")))
Mar 23 2023 02:30 PM
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.