Sort text values by length
The image above demonstrates a formula in cell D3 that sorts values based on character length, the value with the most characters are at the top, and the value with the least amount of characters is at the bottom.
I recommend Excel 365 users this article: Sort by word length. That formula is considerably smaller and easier to understand.
Array formula in D3:
copied down as far as needed.
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.
Explaining formula in cell B2
Step 1 - Calculate the k-th largest string length
In order to return a new value in each cell, the formula uses an expanding cell reference to return the next largest value. The LEN function returns the character length of a value, the ROWS function returns the number of rows in a cell reference. The LARGE function returns the k-th largest value. LARGE( array, k)
LARGE(LEN($B$3:$B$20), ROWS($A$1:A1))
becomes
LARGE(LEN($B$3:$B$20), 1)
becomes
LARGE({2; 3; 3; 2; 4; 3; 3; 3; 2; 6; 4; 3; 3; 2; 3; 1; 3; 2}, 1)
and returns 6.
Step 2 - Check previously displayed values in cells above
The COUNTIF function counts cells in cell range based on a condition or criteria. The first argument contains an expanding cell reference that lets the formula keep track of already shown values.
COUNTIF($F$2:F2, $B$3:$B$20)
becomes
COUNTIF("Sorted length",{"AB"; "CDE"; "BNA"; "BA"; "DDED"; "KJH"; "LKJ"; "DFV"; "CV"; "TTRYTU"; "DVDS"; "SGE"; "TFT"; "CC"; "BNA"; "T"; "DSF"; "AB"})
and returns
{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
Step 3 - Check if the count of prior values are less than the total count of each value
(COUNTIF($D$2:D2,$B$3:$B$20)<COUNTIF($B$3:$B$20,$B$3:$B$20)
becomes
{0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}<{2; 1; 2; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 2; 1; 1; 2}
and returns
{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}.
Step 4 - Match length to array
The MATCH function returns the relative position in a cell range or array of a given value.
MATCH(LARGE(LEN($B$3:$B$20), ROWS($A$1:A1)), LEN($B$3:$B$20)*(COUNTIF($F$2:F2, $B$3:$B$20)<COUNTIF($B$3:$B$20, $B$3:$B$20)), 0)
becomes
MATCH(6, LEN($B$3:$B$20)*(COUNTIF($F$2:F2, $B$3:$B$20)<COUNTIF($B$3:$B$20, $B$3:$B$20)), 0)
becomes
MATCH(6, LEN($B$3:$B$20)*{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}, 0)
becomes
MATCH(6, {2; 3; 3; 2; 4; 3; 3; 3; 2; 6; 4; 3; 3; 2; 3; 1; 3; 2}*{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}, 0)
becomes
MATCH(6, {2; 3; 3; 2; 4; 3; 3; 3; 2; 6; 4; 3; 3; 2; 3; 1; 3; 2}, 0)
and returns 10.
Step 5 - Return value
The INDEX function returns a value based on row number (and column number if needed)
INDEX($B$3:$B$20, MATCH(LARGE(LEN($B$3:$B$20), ROWS($A$1:A1)), LEN($B$3:$B$20)*(COUNTIF($F$2:F2, $B$3:$B$20)<COUNTIF($B$3:$B$20, $B$3:$B$20)), 0))
becomes
INDEX($B$3:$B$20, 10)
amd returns TTRYTU in cell D3.
Get Excel *.xlsx file
Sort values category
Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]
This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition. […]
Array formula in D5: =SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1)) How to create an array formula Copy array formula Select cell D5 […]
This article demonstrates a formula that extracts values based on a condition and sorts the returned values based on values […]
The image above shows a table with two columns in cell range B3:C16, it contains random text values in column […]
This article demonstrates a formula that sorts cell values by their frequency, in other words, how many times a value […]
Table of Contents Sort text from two columns combined (array formula) How to create an array formula How to copy […]
This article demonstrates a macro and a formula that allows you to sort delimited data in a cell or cell […]
This article demonstrates a formula that sorts items arranged horizontally based on the adjacent numbers, every other column contains a […]
This article demonstrates how to sort a specific column in an Excel defined Table based on event code. The event […]
Question: How do I sort a range alphabetically using excel array formula? Answer: Cell range $B$2:$E$5 contains text values in random […]
The formula in cell B8 extracts a list sorted based on frequency. Array formula in B8: =TEXTJOIN("", TRUE, IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, […]
Ralee asks in in this blog post: Sort values in parallel (array formula) If there is information in adjacent columns, […]
Overview The array formula in cell range C2:C6 creates a random list from the values in cell range A2:A6. Array […]
In this article, I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The […]
The following array formula extracts duplicate values sorted from A to Z from cell range B3:B21. Excel array formula in […]
I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]
This article describes a formula that sorts values arranged in a column from A to z by every other value. […]
This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
2 Responses to “Sort text values by length”
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.
Dear Oscar,
The suggested formula is going to fit in those countries who uses a COMMA as Decimal mark. (France, Austria, Germany etc').
In Israel (and in many more countries) we use a DOT as a decimal mark.
I would suggest to add this as a comment beside your suggested formula.
Have a nice weekend,
--------------------------
Michael (Micky) Avidan
“Microsoft®” Excel MVP – Excel (2009-2018)
ISRAEL
Michael Avidan,
thank you for pointing that out. I will update this article.