# LARGE formula - is there an equivalent for text data?

#### JodiEHBK

##### New Member
What is the equivalent formula to the LARGE formula that will sort text rather than number?

Using the formula =LARGE(\$B2:\$H2, columns (\$K2:K2)) I can sort numerical data and rewrite to the second array

Is there an equivalent formula that will enable be to do the same with text entered into cells instead of numerical value?

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Consider text in A1:A100

=INDEX(A:A, 1000*MOD(SMALL(COUNTIF(A1:A100, "<="&A1:A100)+ROW(A1:A100)/1000, 1), 1), 1)

The red 1 can be changed to get the second smallest text in A1:A100, etc.

(This is untested, it might have to be entered with Ctrl-Shift-Enter)

Thanks Mike,

I'll experiment with this. Most appreciated.

Jodi

Consider text in A1:A100

=INDEX(A:A, 1000*MOD(SMALL(COUNTIF(A1:A100, "<="&A1:A100)+ROW(A1:A100)/1000, 1), 1), 1)

The red 1 can be changed to get the second smallest text in A1:A100, etc.

(This is untested, it might have to be entered with Ctrl-Shift-Enter)

Thanks Mike,

I'll experiment with this. Most appreciated.

Jodi

Edit: Ignore, balderdash, it be broken.

If you mean sorting alphabetically, then:

Excel 2003
ABCDE
1UnsortedSortedUnsortedSorted
2467982EqZN
3127934PbXE
4639875WhWH
5934770MfUX
626649FcSW
7649639CdSO
854555LqRW
967481ZoPB
10481474RwMZ
11555467EfME
12438438UyLQ
1335313XfIB
14982236MzFE
15875225SpFB
16225127CeEQ
1747467BgEE
1823654SxCE
1931335FfCC
2077026IcBF
Sheet1
Cell Formulas
RangeFormula
B2=LARGE(\$A\$2:\$A\$20,ROWS(A\$1:A1))
E2{=CHAR(ROUNDDOWN(LARGE(CODE(LEFT(UPPER(\$D\$2:\$D\$20),1))+CODE(MID(UPPER(\$D\$2:\$D\$20),2,1))/1000,ROWS(A\$1:A1)),0))&CHAR(MOD(LARGE(CODE(LEFT(UPPER(\$D\$2:\$D\$20),1))+CODE(MID(UPPER(\$D\$2:\$D\$20),2,1))/1000,ROWS(A\$1:A1)),1)*1000)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Last edited:
Trying again:

Excel 2003
ABCDEF
1UnsortedSortedUnsortedFirst 2 letter codeAlphabetical
2467982Equine90079Zombie
3127934Plumbing88073Xi
4639875Wharf87072Wharf
5934770Magnificent85078Undulating
626649Ferrous83080Special
854555Loquatious82087Rwanda
967481Zombie80076Plumbing
10481474Rwanda77079Molybedenum
11555467Effortless77065Magnificent
12438438Undulating76079Loquatious
1335313Xi73067Ichor
14982236Molybedenum70076Fluffy
15875225Special70069Ferrous
16225127Certify69081Equine
1747467Background69070Effortless
1823654Sarkozy67069Certify
2077026Ichor66065Background
Sheet1
Cell Formulas
RangeFormula
B2=LARGE(\$A\$2:\$A\$20,ROWS(A\$1:A1))
E2{=LARGE(CODE(LEFT(UPPER(\$D\$2:\$D\$20),1))*1000+CODE(MID(UPPER(\$D\$2:\$D\$20),2,1)),ROWS(A\$1:A1))}
F2{=INDEX(\$D\$2:\$D\$20,MATCH(CHAR(ROUNDDOWN(E2/1000,0))&CHAR(MOD(E2,1000)),LEFT(UPPER(\$D\$2:\$D\$20),2),0),)}
Press CTRL+SHIFT+ENTER to enter array formulas.

It will break if there are two items with the same first 2 letters, or if a word is less than 2 letters. Both can be fixed if needed, but whatever.

Thanks, Sal. This worked well

Jodi

Replies
3
Views
199
Replies
1
Views
172
Replies
7
Views
201
Replies
4
Views
262
Replies
6
Views
198

1,203,111
Messages
6,053,571
Members
444,673
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back