How to Sort Dewey Decimal Codes with Numbers and Text in Excel
Question details
The user needs to sort Dewey Decimal classification codes that contain both numeric prefixes and text suffixes in a proper order.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing a library catalog or dataset where items are categorized by Dewey Decimal codes.
- Observed behavior
- Standard sorting methods fail to correctly order codes like "398 FOLK" or "942 TUDORS" alongside purely numeric codes without specialized formulas or helper columns.
Ensure your data is organized into a single column or define a named range for your array formula to reference, and verify that your version of Excel supports dynamic array functions like LET, TEXTBEFORE, and SORTBY.
Sort Dewey Decimal Codes Using a Dynamic Array Formula
Use the LET and SORTBY functions to dynamically extract and sort both the numeric and text parts of the Dewey codes.
This method is ideal for modern versions of Excel that support dynamic arrays. It splits the code by the space character, evaluates the number, and then sorts the entire list automatically.
Click on an empty cell where you want the sorted list of Dewey Decimal codes to appear.
Type the formula: =LET(i,--TEXTBEFORE(A2:A100," "),a,TEXTAFTER(A2:A100," "),SORTBY(A2:A100,i,1,a,1)). Replace A2:A100 with the actual range containing your Dewey codes.
Press Enter. The formula will automatically spill the sorted results down the column.
Sort Using Helper Columns
Extract the numeric and text portions into separate columns to use Excel's standard sorting feature.
Sort Using a Custom List
Define a specific sorting order manually for smaller datasets.
Organize Your Library Data with WPS Spreadsheet
WPS Spreadsheet provides powerful data sorting capabilities, including support for advanced array formulas and multi-level sorting, making it easy to organize complex datasets like Dewey Decimal codes.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your Dewey Decimal codes.
- 2. Apply sorting formulas: Use the same dynamic array formulas or helper column methods as you would in Excel to separate and sort the data.
- 3. Utilize advanced sorting tools: Navigate to the Data tab to access intuitive multi-level sorting and filtering tools to perfectly organize your library catalog.
Frequently Asked Questions
Why does standard A-Z sorting fail for Dewey Decimal codes?
Standard sorting treats the entire cell as text when it contains mixed characters. This means a code starting with "10" might appear before a code starting with "2", because the character "1" comes before "2" alphabetically, ignoring the actual numeric value of the prefix.
What if my Dewey code doesn't have a space between the number and text?
If there is no space delimiter (e.g., "398FOLK"), you will need to use more complex formulas involving the MIN and FIND functions to locate the first text character, or use Flash Fill to manually train the spreadsheet to separate the numbers from the text.
Can I sort Dewey codes in older versions of Excel?
Yes, but older versions do not support dynamic array functions like LET, TEXTBEFORE, or SORTBY. You will need to use the Helper Columns method to extract the numbers and text using LEFT, MID, and FIND functions, then use the standard Data > Sort feature.
How do I handle Dewey codes that are purely numeric in the array formula?
Purely numeric codes will cause an error with TEXTBEFORE and TEXTAFTER if there is no space to use as a delimiter. You can wrap these functions in IFERROR to return the original number if no space is found, ensuring the SORTBY function still processes the row correctly.
