SMALL function for text
This article demonstrates a formula that sorts text values based on character length, the Excel 365 dynamic array formula is considerably smaller and is a great example of how amazing the new Excel 365 functions are.
The image above shows the result in cell E3 and cells below, the formula is returning values from cell range B3:B12 from small to large (ascending order). Cell range C3:C12 shows the string length of the corresponding cell on the same row.
Table of Contents
1. SMALL function for text
The array formula in column E, shown in above picture sorts text values from column B. The Length columns prove that they are sorted by text length from small to large. Don't use this formula if you are an Excel 365 subscriber, read section 2.
1.1 How to enter an array formula
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
1.2 Explaining formula
Step 1 - Calculate string length
The LEN function returns a number representing the number of characters in a given cell.
LEN(text)
LEN($B$3:$B$12)
becomes
LEN({"one"; "two"; "three"; "four"; "five"; "six"; "seven"; "eight"; "nine"; "ten"})
and returns
{3; 3; 5; 4; 4; 3; 5; 5; 4; 3}.
Step 2 - Extract the k-th smallest number
The SMALL function returns the k-th smallest value from a group of numbers.
SMALL(array, k)
SMALL(LEN($B$3:$B$12), ROWS($A$1:A1))
becomes
SMALL({3; 3; 5; 4; 4; 3; 5; 5; 4; 3}, ROWS($A$1:A1))
The ROWS function allows you to calculate the number of rows in a cell range.
ROWS(array)
The cell reference $A$1:A1 has two parts, an absolute and a relative part. When you copy the cell and paste it to the cells below the relative part changes accordingly. This will calculate a new number in each cell, the first cell evaluates to 1, the next cell below evaluates to two, and so on.
SMALL({3; 3; 5; 4; 4; 3; 5; 5; 4; 3}, ROWS($A$1:A1))
becomes
SMALL({3; 3; 5; 4; 4; 3; 5; 5; 4; 3}, 1)
and returns 3.
Step 3 - Compare the result to the array
The equal sign is a logical operator, it allows you to compare value to value or in this case value to an array. The result is a boolean value, TRUE or FALSE.
SMALL(LEN($B$3:$B$12), ROWS($A$1:A1))=LEN($B$3:$B$12)
becomes
3=LEN($B$3:$B$12)
becomes
3={3; 3; 5; 4; 4; 3; 5; 5; 4; 3}
and returns
{TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE}.
Step 4 - Count previous results above and compare result with zero
The COUNTIF function calculates the number of cells that is equal to a condition.
COUNTIF(range, criteria)
(COUNTIF($E$2:E2, $B$3:$B$12)=0
becomes
{0;0;0;0;0;0;0;0;0;0}=0
and returns
{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}.
This means that there have not been displayed any values above yet.
Step 5 - Multiply arrays
We will now multiply the array containing character length with the array containing where the k-th smallest number is.
(SMALL(LEN($B$3:$B$12), ROWS($A$1:A1))=LEN($B$3:$B$12))*(COUNTIF($E$2:E2, $B$3:$B$12)=0)
becomes
{TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE}*(COUNTIF($E$2:E2, $B$3:$B$12)=0)
becomes
{TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE}*{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}
and returns
{1; 1; 0; 0; 0; 1; 0; 0; 0; 1}.
When you perform a calculation to boolean values the result is always the numerical equivalent.
TRUE = 1 and FALSE = 0 (zero)
Step 6 - Replace TRUE with corresponding row number
The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.
IF(logical_test, [value_if_true], [value_if_false])
IF((SMALL(LEN($B$3:$B$12), ROWS($A$1:A1))=LEN($B$3:$B$12))*(COUNTIF($E$2:E2, $B$3:$B$12)=0), MATCH(ROW($B$3:$B$12), ROW($B$3:$B$12)), "")
becomes
IF({1; 1; 0; 0; 0; 1; 0; 0; 0; 1}, MATCH(ROW($B$3:$B$12), ROW($B$3:$B$12)), "")
The ROW and MATCH function combined creates a sequence of numbers from 1 to n where n is the number of rows in $B$3:$B$12
MATCH(ROW($B$3:$B$12), ROW($B$3:$B$12))
becomes
MATCH({3;4;5;6;7;8;9;10;11;12}, {3;4;5;6;7;8;9;10;11;12})
and returns
{1; 2; 3; 4; 5; 6; 7; 8; 9; 10}.
IF({1; 1; 0; 0; 0; 1; 0; 0; 0; 1}, MATCH(ROW($B$3:$B$12), ROW($B$3:$B$12)), "")
becomes
IF({1; 1; 0; 0; 0; 1; 0; 0; 0; 1}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}, "")
and returns
{1; 2; ""; ""; ""; 6; ""; ""; ""; 10}.
Step 7 - Extract the smallest row number
SMALL(IF((SMALL(LEN($B$3:$B$12), ROWS($A$1:A1))=LEN($B$3:$B$12))*(COUNTIF($E$2:E2, $B$3:$B$12)=0), MATCH(ROW($B$3:$B$12), ROW($B$3:$B$12)), ""), 1)
becomes
SMALL({1; 2; ""; ""; ""; 6; ""; ""; ""; 10}, 1)
and returns 1.
Step 8 - Get value
The INDEX function returns a value from a cell range, you specify which value based on a row and column number.
INDEX($B$3:$B$12, SMALL(IF((SMALL(LEN($B$3:$B$12), ROWS($A$1:A1))=LEN($B$3:$B$12))*(COUNTIF($E$2:E2, $B$3:$B$12)=0), MATCH(ROW($B$3:$B$12), ROW($B$3:$B$12)), ""), 1))
becomes
INDEX($B$3:$B$12, 1)
and returns "one" in cell F3.
If you are looking for a formula that sorts text alphabetically, check this out: Sort a column alphabetically
2. SMALL function for text - Excel 365
The formula in cell E3 sorts the text values in cell range B3:B12 based on character length from small to large.
Dynamic array formula in cell E3:
2.1 How to enter a dynamic array formula
The formula in cell E3 works only in Excel 365, it is entered as a regular formula however, it spills to cells below as far as needed.
2.2 Explaining formula
Step 1 - Calculate string length
The LEN function returns a number representing the number of characters in a given cell.
LEN(text)
LEN(B3:B12)
becomes
LEN({"one"; "two"; "three"; "four"; "five"; "six"; "seven"; "eight"; "nine"; "ten"})
and returns
{3; 3; 5; 4; 4; 3; 5; 5; 4; 3}.
Step 2 - Sort by string length
The SORTBY function allows you to sort values from a cell range or array based on a corresponding cell range or array. It sorts values by column but keeps rows.
SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)
SORTBY(B3:B12, LEN(B3:B12), 1)
becomes
SORTBY(B3:B12, {3; 3; 5; 4; 4; 3; 5; 5; 4; 3}, 1)
and returns
{"one"; "two"; "six"; "ten"; "four"; "five"; "nine"; "three"; "seven"; "eight"}.
Get Excel file
Small category
This article demonstrates ways to sort numbers from smallest to largest ignoring duplicate numbers. Table of Contents SMALL function with […]
This article demonstrates how to extract multiple numbers based on a condition and return a sorted list from small to […]
This article shows how to create a formula that sorts numbers from small to large excluding zeros. I will also […]
Excel categories
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
