## 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.

**Contact Oscar**

You can contact me through this contact form

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.