Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to sort data by case sensitive in Excel?

In excel, you can sort values in descending or ascending order, but have you ever wanted to sort data by case sensitive? Actually, with Excel’s Sort function, you can sort data by case sensitive, but its sorting order maybe not the answer you want. In sometimes, you want to sort all uppercase in the top and follow the lowercase as screenshot 1 shown, but in sometimes, you want to sort the order like a, A, b, B… as screenshot 2 shown. Now I will tell you the tricks to quickly solve these tasks in Excel.

Screenshot1                           Screenshot2

arrow blue right bubble Sort data in case sensitive with help column (all uppercase in top, and follow lowercase)


To sort the uppercase data in top, and then follow the lowercase, you can use a formula to create a help column, and then sort the help column.

Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.

1. Select a blank cell next to your data, then type this formula =(CODE(LEFT(A1))>90) + 0 into it, then press Enter key, and then drag the auto fill handle down to apply this formula. You can see there are some number 0 and 1 displayed in the cells, and number 0 indicates the first character is an uppercase letter in corresponding cell, and number 1 indicates the first character is not an uppercase letter in corresponding cell. See screenshots:

doc sort by case sensitive 3doc sort by case sensitive 4

2. Then keep these formula cells selected, and click Data, then select Sort Smallest to Largest or Sort Largest to Smallest as you need. To sort uppercase at top, select Sort Smallest to Largest. See screenshot:

doc sort by case sensitive 5

3. Then in the popping Sort Warning dialog, check Expand the selection option. See screenshot:

doc sort by case sensitive 6

4. Click Sort. Now the all uppercases are at the top, and lowercase at the bottom.

doc sort by case sensitive 7

You can remove the helper column if you do not need it any more.


arrow blue right bubble Sort data in case sensitive with Defined Function (all uppercase in top, and follow lowercase)

If you like to study at VBA, you can use below VBA code to do as the same result as helper column.

Kutools for Excel, with more than 120 handy Excel functions, enhance working efficiency and save working time.

1. Press Alt + F11 keys to enable Microsoft Visual Basic for Applications window, then click Insert > Module.

2. Then in the new Module window, paste below VBA code into it.

VBA: Sort data case sensitive.

Function StrToHex(Str) As Variant
‘UpdatebyKutoolsforExcel20150921
    Dim xStr As String
    Dim I As Integer
    If Not (VarType(Str) = vbString) Then
        StrToHex = Str
    Else
        For I = 1 To Len(Str)
          xStr = xStr & Format(Hex(Asc(Mid(Str, I, 1))), "00")
        Next I
        StrToHex = xStr
    End If
End Function

3. Then save the code and go back to your worksheet, and then select a blank cell next to your data, and type this formula =StrToHex(I1) into it, then drag autofill handle down to apply this formula to cells. See screenshot:

doc sort by case sensitive 8

4. Then you can see there are some numbers in the cells, and keep the formula cells selected, and go to Data tab, and select Sort Smallest to Largest, see screenshot:

doc sort by case sensitive 9

5. Then in the popping dialog, check Expand the Selection.

doc sort by case sensitive 10

6. Click Sort. Now the all uppercases are at the top, and lowercase at the bottom.

doc sort by case sensitive 11

You can delete the formula cells as you need.


arrow blue right bubble Sort data in case sensitive with Kutools for Excel (sort data like this order: a, A, b, B, c. C…)

If you want the data sort like below screenshot shown, above method cannot help you. However, you can use Kutools for Excel’s Advanced Sort utility to help you finish this task quickly.

doc sort by case sensitive 2

Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.

After free installing Kutools for Excel, please do as below:

1. Select the data you want to sort, and click Enterprise > Advanced Sort. See screenshot:

doc sort by case sensitive 13

2. In the Advanced Sort dialog, select the column you want to sort, and select Values under Sort On list, then select A to Z in the Order list, and remember to check Case sensitive checkbox, if your data has no header, uncheck My data has headers. See screenshot:

doc sort by case sensitive 14

3. Click Ok. Now the data has been sorted as below screenshot shown.

doc sort by case sensitive 15

With Kutools for Excel’s Advanced Sort function, you can sort data by weekday, frequency, mail domain, absolute value, text length and so on.

doc sort by case sensitive 16

pay attention1If you are interested in this addi-in, click here to download the 60-days free trial.


Recommended Productivity Tools for Excel

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.