Explanation

We have a list of 12 codes in Column B.

The 2-letter prefix and 4-digit number should be ignored during sorting.

To do this, we use a combination of theTEXTAFTER functionand theTEXTBEFORE function.

Using TEXTAFTER to extract text after the first hyphen

This can be a bit confusing.

The main thing to keep in mind is that TEXTBEFORE is working with theoutputfrom TEXTAFTER.

The result looks like this:

In this version, we’ve incorporated theLET functionto keep things streamlined.

Using TEXTAFTER to extract text after the first hyphen

The range or array used to sort does not need to appear in results.

TEXTBEFORE Function

The Excel TEXTBEFORE function returns the text that occurs before a given substring or delimiter.

TEXTAFTER Function

The Excel TEXTAFTER function returns the text that occurs after a given substring or delimiter.

Using TEXTAFTER to extract text after the first hyphen

Using TEXTAFTER to extract text after the first hyphen

Using TEXTAFTER to extract text after the first hyphen

Using TEXTAFTER to isolate the color

SORTBY sorts the codes by the color substring

Using the same approach to sort location data by state

Extending the formula to sort by state and then by city

Excel formula: Sort by custom list

Excel SORTBY function

Excel TEXTBEFORE function

Excel TEXTAFTER function

Article image

Article image

Using TEXTAFTER to extract text after the first hyphen

Using TEXTAFTER to isolate the color

SORTBY sorts the codes by the color substring

Using the same approach to sort location data by state