Sort Text

[Dinesh121]

I went through the forum and viewed text sorting using formula's but could not get..

What I would like to do is I have a list if text as below


A

Z

D

B

C


To

A

B

C

Z

D


I want to sort it to in ascending order Except D as i always want this in last .

 

[Luke M]

Using this article as reference:

http://chandoo.org/wp/2008/10/22/sorting-text-in-excel-using-formulas/


Formula to generate corrected numbers for sorting:

=IF(A2="D",90000000000+ROW(),COUNTIF($A$2:$A$14,"<="&A2))


Assuming previous formula is in col B, formula to generate the sorted list would be:

=INDEX($A$2:$A$14,MATCH(SMALL($B$2:$B$14,ROW(A1)),$B$2:$B$14,0))

 

[NARAYANK991]

Hi Dinesh ,


An alternative is to just sort your data using a Custom list ; add the following list :


A,B,C,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,D


Narayan

 

[Dinesh121]

Hey Luke ,


The Formula Works Perfect ...Thanks A ton.


@ NARAYANK991 thanks a lot for the alternate method.


-Dinesh

 

[Luke M]

Glad I could help. Have a great weekend!