exceljet.net Open in urlscan Pro
2600:3c00::f03c:91ff:fee7:ea6e  Public Scan

Form analysis
4 forms found in the DOM

GET /search

<form class="search-form" action="/search" method="get" id="header-search" accept-charset="UTF-8">
  <input type="text" name="keys" value="" placeholder="Search" size="15" maxlength="128" class="form-text search-text">
  <input type="submit" name="op" value="Search" class="form-submit search-submit">
</form>

GET /search

<form class="search-form" action="/search" method="get" id="header-search" accept-charset="UTF-8">
  <input type="text" name="keys" value="" placeholder="Search" size="15" maxlength="128" class="form-text search-text">
  <input type="submit" name="op" value="Search" class="form-submit search-submit">
</form>

POST /antibot

<form class="webform-client-form-3946 webform-client-form webform-client-form webform-client-form-3946 antibot" action="/antibot" method="post" id="webform-client-form-3946" accept-charset="UTF-8">
  <div>
    <div class="webform-progressbar">
    </div>
    <div class="form-item webform-component webform-component-textfield webform-component--name">
      <label for="edit-submitted-name">Name </label>
      <input placeholder="Your name" type="text" id="edit-submitted-name" name="submitted[name]" value="" size="60" maxlength="128" class="form-text">
    </div>
    <div class="form-item webform-component webform-component-email webform-component--email">
      <label for="edit-submitted-email">Email </label>
      <input class="email form-text form-email" placeholder="email@example.com" type="email" id="edit-submitted-email" name="submitted[email]" size="60">
    </div>
    <div class="form-item webform-component webform-component-textarea webform-component--problem">
      <label for="edit-submitted-problem">Problem <abbr class="form-required" title="This field is required.">*</abbr></label>
      <div class="form-textarea-wrapper resizable textarea-processed resizable-textarea"><textarea required="required" placeholder="Describe the problem" id="edit-submitted-problem" name="submitted[problem]" rows="5" cols="60"
          class="form-textarea required"></textarea>
        <div class="grippie"></div>
      </div>
    </div>
    <div class="form-item webform-component webform-component-markup webform-component--email-message">
      <p>Your email address is private and not shared.</p>
    </div>
    <input type="hidden" name="details[sid]">
    <input type="hidden" name="details[page_num]" value="1">
    <input type="hidden" name="details[page_count]" value="1">
    <input type="hidden" name="details[finished]" value="0">
    <input type="hidden" name="form_build_id" value="form-j7U0-iCcxO0C8Ioj9VlSU5PBC8aoNZA4Z_Xog_naTfk">
    <input type="hidden" name="form_id" value="webform_client_form_3946">
    <input type="hidden" name="antibot_key" value="">
    <div class="form-actions"><input class="webform-submit button-primary button-primary form-submit ajax-processed" type="submit" id="edit-submit" name="op" value="Submit"></div>
  </div>
</form>

POST https://sendy.exceljet.net/subscribe

<form id="newsletter_signup_footer" class="newsletter-signup-form newsletter-signup-footer-form" action="https://sendy.exceljet.net/subscribe" method="POST" accept-charset="utf-8">
  <label for="email" hidden="">Email</label>
  <input type="email" name="email" placeholder="email address" id="newsletter-signup-footer-email" class="newsletter-signup-footer-email newsletter-signup-email text-input" required="">
  <div style="display:none;">
    <label for="hp">HP</label>
    <input type="text" name="hp" id="newsletter-signup-footer-hp">
  </div>
  <input type="hidden" name="SignupLocation" id="newsletter-signup-footer-SignupLocation" value="footer">
  <input type="hidden" name="list" value="T1JX0z264YewQdKf7gXIig">
  <input type="hidden" name="subform" value="yes">
  <input type="submit" name="submit" id="newsletter-signup-footer-submit" class="newsletter-signup-footer-submit form-submit" value="Subscribe">
</form>

Text Content

Skip to main content
 * Login

Cart
Exceljet
 * Training
 * Videos
 * Functions
 * Formulas
 * Shortcuts
 * Articles


Cart
 * Login

Main menu toggle
 * Training
 * Videos
 * Functions
 * Formulas
 * Shortcuts
 * Articles

 * Login




YOU ARE HERE

Home › Formulas › Sort by substring
 * Previous
 * Next

Dynamic array


SORT BY SUBSTRING


RELATED FUNCTIONS 

SORTBY
TEXTBEFORE
TEXTAFTER
Download Worksheet (22.61 KB)



SUMMARY

To sort data by a substring, you can use the SORTBY function together with the
TEXTBEFORE and TEXTAFTER functions. In the worksheet shown, we are sorting the
codes in column B by color. The formula in cell D5 is:

=SORTBY(B5:B16,TEXTBEFORE(TEXTAFTER(B5:B16,"-"),"-"))

The result in column D is a list of the same codes sorted by color in
alphabetical order.

Powered By

10



Basic SORTBY function example


Share

Next
Stay






GENERIC FORMULA

=SORTBY(range,TEXTBEFORE(TEXTAFTER(range,"-"),"-"))


EXPLANATION 

We have a list of 12 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 this list based on the color substring
so that all codes with the same color are grouped together in the output in
alphabetical order. The 2-letter prefix and 4-digit number should be ignored
during sorting. This is a good example of a situation where the SORTBY function
is necessary instead of the standard SORT function. The formula in cell D5 looks
like this:

=SORTBY(B5:B16,TEXTBEFORE(TEXTAFTER(B5:B16,"-"),"-"))

Working from the inside out, the first step is to isolate the color from the
rest of the code. To do this, we use a combination of the TEXTAFTER function and
the TEXTBEFORE function. First, TEXTAFTER is used to extract text that appears
after the first hyphen:

TEXTAFTER(B5:B16,"-")

You can see the result in the screen below:



The result from TEXTAFTER is delivered directly to the TEXTBEFORE function:

=TEXTBEFORE(TEXTAFTER(B5:B16,"-"),"-")

The TEXTBEFORE function then extracts just the text that occurs before the first
hyphen. This can be a bit confusing. The main thing to keep in mind is that
TEXTBEFORE is working with the output from TEXTAFTER. This means there is
only one hyphen at this point (see the screen above) since TEXTAFTER has already
removed the other hyphen. The screen below shows the result after TEXTBEFORE
runs:



At this point, we have isolated and extracted the color for each code. The final
step is to use the colors to sort the original codes, which is done with the
SORTBY function, which is the outermost function:

=SORTBY(B5:B16,TEXTBEFORE(TEXTAFTER(B5:B16,"-"),"-"))

SORTBY is configured to sort the range B5:B16 using the colors returned by
TEXTBEFORE and TEXTAFTER. The final result looks like this:




SORT BY STATE

There are many other practical cases where the approach described above can be
adapted to sort data by a substring. For example, the worksheet below shows how
you can sort data in the form "City, State ZIP" by State:



The formula in cell D5 is very similar to the original formula above. The
difference is that the delimiters have been adjusted to match the data:

=SORTBY(B5:B16,TEXTBEFORE(TEXTAFTER(B5:B16,", ")," "))


SORT BY STATE AND CITY

Since SORTBY supports sorting by more than one value, we can extend the formula
above to sort by State, and then by City. The result looks like this:



In this version, we've incorporated the LET function to keep things streamlined.
The formula in cell D5 looks like this:

=LET(
data,B5:B16,
states,TEXTBEFORE(TEXTAFTER(data,", ")," "),
cities,TEXTBEFORE(data,","),
SORTBY(data,states,1,cities,1)
)

This is how the formula works:

 1. Open with the LET function so that we can use variables.
 2. Declare a variable named "data" and assign the range B5:B16 as the value.
 3. Declare a variable named "states" and use TEXTBEFORE and TEXTAFTER to
    extract just the state abbreviations. 
 4. Declare a variable named "cities" and use TEXTBEFORE to extract just the
    city names.
 5. Use the SORTBY function to sort data by states and then by cities.

This is a nice example of how the LET function can organize a more complex
formula in a way that makes it easier to read and understand.


RELATED FORMULAS


SORT BY CUSTOM LIST

In this example, we are sorting a table with 10 rows and 3 columns. In the range
J5:J7 (the named range custom ), the colors "red", "blue", and "green" are
listed in the desired sort order. The goal is to sort the table using values in
the Group column in this same custom order. The SORTBY function...


RELATED FUNCTIONS


SORTBY FUNCTION

The Excel SORTBY function sorts the contents of a range or array based on the
values from another range or array. 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. In cases where multiple delimiters appear in the text,
TEXTBEFORE can return text before the nth occurrence of the delimiter.


TEXTAFTER FUNCTION

The Excel TEXTAFTER function returns the text that occurs after a given
substring or delimiter. In cases where multiple delimiters appear in the text,
TEXTAFTER can return text after the nth occurrence of a delimiter....


RELATED VIDEOS


BASIC SORTBY FUNCTION EXAMPLE

In this video, we’ll look at a basic example of sorting with the SORTBY function
. In this worksheet, we have a list of names, scores, and groups. Currently the
data is not sorted. Our goal is to sort this data by group, then by score in
descending order. I’ll start off by placing the cursor in...


SORT BY CUSTOM LIST WITH SORTBY

In this video, we'll look at how to sort with SORTBY function using a custom
list. One challenge that comes up frequently when sorting is a need to sort in a
custom order. For example, in this worksheet, we have a list of opportunities,
and we want to sort the list in the order that stages appear...


Author


DAVE BRUNS

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help
you work faster in Excel. We create short videos, and clear examples of
formulas, functions, pivot tables, conditional formatting, and charts.


RELATED INFORMATION


FORMULAS

 * Sort by custom list


FUNCTIONS

 * SORTBY Function
 * TEXTBEFORE Function
 * TEXTAFTER Function


VIDEOS

 * Basic SORTBY function example
 * Sort by custom list with SORTBY


TRAINING

 * Dynamic Array Formulas


DOWNLOAD 100+ IMPORTANT EXCEL FUNCTIONS

Get PDF Guide


TOPICS

 * Formula Basics
 * Formula Examples
 * Conditional Formatting
 * Pivot Tables
 * Excel Tables
 * Excel Charts
 * Shortcuts
 * More...


KEY FUNCTIONS

 * IF function
 * VLOOKUP function
 * XLOOKUP function
 * FILTER function
 * SUMIFS function
 * COUNTIFS function
 * SUMPRODUCT function
 * INDEX and MATCH
 * 350 Excel Functions


I'm the "Excel guru" at my job. Your site is one of the few I count on; your
explanations are very clear and well done, and for sophisticated applications.
Sam
More Testimonials


GET TRAINING


QUICK, CLEAN, AND TO THE POINT TRAINING

Learn Excel with high quality video training. Our videos are quick, clean, and
to the point, so you can learn Excel in less time, and easily review key topics
when needed. Each video comes with its own practice worksheet.

View Paid Training & Bundles

Report a problem with this page


HELP US IMPROVE EXCELJET

You must have JavaScript enabled to use this form.
Name
Email
Problem *


Your email address is private and not shared.


Exceljet
Quick, clean, and to the point


RESOURCES

 * Training
 * Videos
 * Functions
 * Formulas
 * Shortcuts
 * Articles


ABOUT US

 * About
 * Testimonials
 * Topics
 * Contact


NEWSLETTER SIGN-UP

Work faster in Excel. Join more than 100,000 people who get weekly tips from us.
Email
HP
© 2012-2023 Exceljet. Terms of use
 * Visit our Twitter page