< Go Back

Sort text and numbers with formula

SEQUENTIAL ARRANGEMENT OF TEXT AND NUMBERS WITH EXCEL

To sequentially arrange text and numbers with excel based on ranks assigned to the text and numbers in another cell, we can use the COUNTIF and ISTEXT functions. The examples below show how to deal with duplicates, combined text and numbers, and sorted values. In example 1 below, the formula for Cell E7 is given below.

=COUNTIF(D7:D13,"<="&D7)+(COUNT(G7:G13)*ISTEXT(D7))

HOW TO SEQUENTIALLY ARRANGE TEXT AND NUMBERS WITH EXCEL

Example 1

Step 1

Input your data as shown in figure 1 below.

Step 2

Click on Cell E7 as shown in figure 2 and type the content as shown on the figure into the cell.

=COUNTIF(D7:D13,"<="&D7)+(COUNT(G7:G13)*ISTEXT(D7))

Step 3

Click on ENTER and your result should be as shown in figure 3

To treat the raw data on Cell D12 (200), simply change D7 in the formula to D12 as shown.
Click on OK and get your result as shown below.

EXPLAINING THE COUNTIF FUNCTION

=COUNTIF(D7:D13,"<="&D7)+(COUNT(G7:G13)*ISTEXT(D7))

=COUNTIF(D7:D13,"<="&D7)- This COUNTIF function produces a rank provided all of the data are either numeric or alphabetic all through. So, if your data contains only text or only numeric characters, this function is okay and there won’t be need to type the function from the plus sign.

(COUNT(G7:G13)*ISTEXT(D7))This COUNTIF function is added due to both text and numbers that are present on the data. This function helps to count numeric values present in the raw data.

Example 2

Example 1 above discusses how to rank your data once you have the sorted arrangement for them. This example will discuss how you will rank the raw data above.

Type the raw data and the have a cell for rank as shown in figure 1. Do not type the sorted data. Leave it out.

Click on Cell E7 and type the content as shown on the fx bar. Click on ENTER and your result should be as shown in figure 7.

You can treat the others by changing D7 on the fx bar to D8, D9, and so on.

Example 3

RANKING DUPLICATE VALUES

Let us change the data in Cell D13 to 200. This results in cell D12 and D13 having the same values. To rank them, use the formula below.

  • Formula for D12

=COUNTIF(D7:D13,"<"&D12)+(COUNT(D7:D13)*ISTEXT(D12))+COUNTIF($D$12:D12,D12)

  • Formula for D13

=COUNTIF(D7:D13,"<"&D12)+(COUNT(D7:D13)*ISTEXT(D12))+COUNTIF($D$12:D12,D12)

When you are done, you should have something like this below.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar