Sort text values by length
Array formula in B2:
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.
Download Excel *.xlsx file
Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]
Extract a unique distinct list sorted from A to Z ignore blanks
The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A […]
Sort dates within a date range
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 […]
Lookup and return multiple sorted values based on corresponding values in another column
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 […]
Sort column based on frequency
Question: How do I create a new unique distinct list from a column. I also want the list sorted from large […]
Sort text cells alphabetically from two columns
Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined […]
Denisa asks: I have a problem and i cant figure it out, even if i'm seraching for 2 days. I […]
Sort values in an Excel table programmatically [VBA]
This article demonstrates how to sort a specific column in an Excel defined Table based on event code. The event […]
Sort values in a cell based on a delimiting character [VBA]
This article demonstrates a macro that allows you to sort delimited data in a cell or cell range from A […]
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.