Hi - Dave here.
Happy Friday!
In my last email, I shared a
video demo of how you can use the SORTBY function to perform a custom sort.
Before we leave the topic of SORTBY, I wanted to share a simpler example.
In the worksheet below, we have a list of codes in Column B. Each code consists of a prefix (two letters), a color (variable), and a 4-digit number, all separated by hyphens (e.g., AX-Red-6387).
The goal is to sort the codes by the color substring. In cell D5, the formula we use to solve this problem looks like this:
=SORTBY(B5:B16,TEXTBEFORE(TEXTAFTER(B5:B16,"-"),"-"))
You can see the result below:
[
Download the workbook and read the full explanation]
This is a good example of a situation where the SORTBY function is necessary instead of the standard SORT function. In brief, we use TEXTBEFORE and TEXTAFTER to isolate the color, then we feed the colors into SORTBY as the values to use for sorting. Download the workbook and have a look yourself.
Note: you will need Excel 365 to use this formula.
Excel formulas
We maintain a list of over 500 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
