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.
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.
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:
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:
3. Then in the popping Sort Warning dialog, check Expand the selection option. See screenshot:
4. Click Sort. Now the all uppercases are at the top, and lowercase at the bottom.
You can remove the helper column if you do not need it any more.
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.
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 Function3. 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:
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:
5. Then in the popping dialog, check Expand the Selection.
6. Click Sort. Now the all uppercases are at the top, and lowercase at the bottom.
You can delete the formula cells as you need.
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.
After free installing Kutools for Excel, please do as below:
1. Select the data you want to sort, and click Kutools Plus > Sort > Advanced Sort.
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:
3. Click Ok. Now the data has been sorted as below screenshot shown.
With Kutools for Excel’s Advanced Sort function, you can sort data by weekday, frequency, mail domain, absolute value, text length and so on.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, 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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in