Go Back

What is the Basic Text Sort Formula in Excel?

Figure 1. of Basic Text Sort Formula in Excel.

In order to alphabetically sort text values in Excel, we can utilize the COUNTIF Function. This tutorial will assist all levels of Excel users in sorting text values.

Generic Formula

=COUNTIF(range,"<="&A1)

When the text is compared by Excel, it will automatically rank them by  deciding which of the values provided is greater.

How to use the COUNTIF Function in Excel.

In the example illustrated below, we are going to utilize the COUNTIF Function to rank the listed countries in alphabetical order..

We can achieve this by following two simple steps.

  1. We must first list the COUNTRIES in one column. We will label the next column HELPER where the COUNTIF Function will determine the number value of each country. .

We can use the HELPER column to store the sorted values according to their rank.

Figure 2. of  Text Data for Sorting in Excel.

  1. The COUNTIF formula syntax which we will input in cell B2 of the example illustrated above, is as follows;

=COUNTIF(A1:A10,"<="&A2)

Figure 3. of the COUNTIF Function in Excel.

Copy the operation syntax in cell B2 down the HELPER column to achieve complete results.

Note

In the example illustrated above;

  • COUNTRIES = cell range A1:A10
  • HELPER = B2:B10

The COUNTIF formula syntax works by using the greater than or equal to (>=) symbol operator together with text.

Figure 4. of Final Result.

Instant Connection to an Expert through our Excelchat Service:

Our live Excelchat Service is here for you. We have Excel Experts available 24/7 to answer any Excel questions you may have. Guaranteed connection within 30 seconds and a customized solution for you within 20 minutes

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Trying to sort cells that contain text ending in "FA"; For instance, each cell contains text such as "John Smith - FA" or "Joan Smith - BA". I'm trying to sort by the last two letters.
Solved by T. J. in 18 mins
Hello - looking for a basic countif formula!
Solved by D. D. in 17 mins
Need help with basic if then formula for numeric values
Solved by A. Y. in 22 mins

Leave a Comment

avatar