Forum Discussion

SaeedShaeri's avatar
SaeedShaeri
Copper Contributor
Jul 01, 2021
Solved

Sorting small and capital letters correcly

I have a shared Excel-based database with some colleagues. One of the database attributes is the type of Schedule, which can be one of the A, a, C, c, G, g, S, s, etc. letters. When I attempt to sort...
excel
  • Celia_Alves's avatar
    Celia_Alves
    Jul 01, 2021

    Hi, SaeedShaeri 

    here are two possible options of doing this without an extra column. I am using Excel for Microsoft 365.

    The first option puts small case before the capital letters when sorting. See if this works for you:

    Click anywhere on the table > File Tab > button Sort & Filter > Custom Sort > button Options > checkmark on "case sensitive" > OK.

    [see pic 1 attached]

     

    The other option is to use a Custom List.

    First create the custom list by going to File > Options > Advanced

    scroll down to General

    click button "Edit Custom Lists..." > click where it says NEW LIST and add your list sorted the way you want.

    Then go to Custom Sort again and under "Order" choose "Custom List":

    [see pic 2 attached]

     

Rajesh_Sinha's avatar
Rajesh_Sinha
Iron Contributor
Jul 02, 2021
If you want to SORT in natural order like Lower Case then Upper like,,, a A b B then use built in option Case Sensitive with Custom Sort as suggested by Celia_Alves , otherwise alphabets can be Sorted like all LOWER CASE letters first then UPPER CASE,,, confirm through reply then I'll show you how !
  • SaeedShaeri's avatar
    SaeedShaeri
    Copper Contributor
    Jul 03, 2021
    Rajesh_Sinha, that is an interesting idea. I am happy to explore this option as well. Please share your suggestion.
    • Rajesh_Sinha's avatar
      Rajesh_Sinha
      Iron Contributor
      Jul 03, 2021

      SaeedShaeri 

       

      To solve the issue as non VBA method, you need a Helper Data Column.

       

       

      How it works:

      • Formula in cell AP2:
      =IF(EXACT(AQ2,UPPER(AQ2)),"UC",IF(EXACT(AQ2,LOWER(AQ2)),"LC",IF(EXACT(AQ2,PROPER(AQ2)),"PC","otr")))

      N.B.

      • Values UC, LC, PC & otr are adjustable, it may be 0 for digits & 1 for Special characters,  2 for Lower case, 3 for Proper, 4 for Upper case.

       

      • Select AO1:AP10, then go to Custom Sort.
      • Set Help Data as First Level & Unsorted as Second Level.

       

      • Finish with Ok.

      You find all small letters/alphabets first then Proper case data and finally capital letters/alphabets.

       

      N.B.

      • For neatness you may hide Help Data column.
      • To follow the hierarchy of data while SORT you may extend your formula to check DIGITS, SPACES & SPECIAL Characters.

       

      • SaeedShaeri's avatar
        SaeedShaeri
        Copper Contributor
        Jul 03, 2021
        Thanks Rajesh_Sinha. I'd follow your approach if I were the sole user. With a shared database, I prefer not to add extra columns.

Resources