How to Sort Blanks, Text, and Numbers in a Custom Excel Order
Question details
The user needs to sort a column in a non-standard order, specifically placing blanks first, alphabetic text second, and numbers last.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing spreadsheet data where default sorting options do not meet specific formatting or reporting requirements.
- Observed behavior
- Standard spreadsheet sorting groups numbers before text or places blanks strictly at the very end, preventing the required custom hierarchy.
Ensure the Developer tab is enabled in your spreadsheet to access the VBA editor, and save a backup copy of your workbook before running custom macros.
Create a Helper Sort Key Using a Custom VBA Function
Since standard sorting does not support this specific arrangement, you can add a VBA function that converts each value into a custom sortable string and use it as a helper column.
This method creates a user-defined function named 'SortSpecial' that re-maps characters. By evaluating the string against a custom constant mapping, it forces blanks to evaluate first, text second, and numbers last.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) window.
Click 'Insert' > 'Module' in the top menu, then paste the following code: Function SortSpecial(ByVal s) As String Const FromSTR = " -/=ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789" Const SortSTR = "#$()0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ" Dim i As Long, j As Long s = UCase(s) For i = 1 To Len(s) For j = 1 To Len(FromSTR) If Mid(s, i, 1) = Mid(FromSTR, j, 1) Then SortSpecial = SortSpecial & Mid(SortSTR, j, 1) Exit For End If Next j Next i SortSpecial = "z" & SortSpecial End Function
Return to your worksheet. Assuming your data is in A2:A40, click cell B2 and enter the formula =SortSpecial(A2). Drag the fill handle down to apply it to all corresponding rows.
Select both your original data column and the new helper column. Go to the Data tab, click 'Sort', and choose to sort by Column B (the helper column) from A to Z.
Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the file type dropdown so your custom function is preserved for future use.
Run Macros and Custom Sorts Smoothly in WPS Spreadsheet
WPS Office provides robust support for advanced data processing, including seamless compatibility with VBA macros and multi-level custom sorting options. You can easily manage complex Excel datasets and automate your sorting workflows for free.
- 1. Open Your Data File: Launch WPS Spreadsheet and open your dataset.
- 2. Access the VBA Editor: Navigate to the Developer tab and click 'VBA Editor' to insert your custom sort code into a new module.
- 3. Set Up the Helper Column: Type the newly created custom function into an adjacent column and fill it down to generate your custom sort keys.
- 4. Sort the Data: Use the built-in 'Data > Sort' feature to instantly reorder your rows based on the helper column.
Frequently Asked Questions
Why are blank cells always sorted at the bottom by default?
In standard spreadsheet logic, blank cells are automatically pushed to the end of the dataset regardless of whether you are sorting in ascending (A-Z) or descending (Z-A) order. To bypass this, a helper column or custom sort key is required.
Do I need to keep the helper column after sorting my data?
If you only need to sort the data once, you can delete the helper column after the sort is complete. However, if your data changes frequently and requires re-sorting, it is highly recommended to keep the helper column intact.
Will this custom VBA sort work if I share the file with someone else?
Yes, as long as you save the file as a Macro-Enabled Workbook (.xlsm). The recipient must also choose to 'Enable Content' or 'Enable Macros' when opening the file on their device for the function to execute properly.
