How to Sort Excel Codes with Two-Digit Years in Chronological Order
Question details
The user needs a method to accurately sort alphanumeric codes that contain two-digit years, ensuring appropriate chronological order.
- Product
- Spreadsheet software
- Device & OS
- not provided
- Scenario
- Sorting a dataset of complex alphanumeric codes where the year is represented by two digits (e.g., '00', '99').
- Observed behavior
- Two-digit years sort incorrectly as text strings, causing years like '00' (2000) to sort earlier than '99' (1999).
Verify the structure of your alphanumeric code to locate the exact position of the two-digit year, and determine an appropriate 'cutoff' year (e.g., 30) for your dataset to distinguish the 1900s from the 2000s.
Use a Helper Column with the LET Formula
This solution translates the two-digit year code into a four-digit year string in a temporary helper column to establish the correct chronological sorting order.
A helper column allows you to extract the two-digit year, test it against a cutoff value, and insert '19' or '20' without destroying your original data format. This specific approach utilizes the LET function to process string extraction cleanly.
Right-click the column header next to your alphanumeric codes and select 'Insert' to create a blank column for your formula calculations.
Assuming your first code is in cell A1 and the year is separated by hyphens, input the formula: `=LET(p,FIND("-",A1),prefix,LEFT(A1,p-3),yr,--MID(A1,p-2,2),suffix,MID(A1,p+1,LEN(A1)),prefix&IF(yr>30,"19","20")&TEXT(yr,"00")&"-"&suffix)`.
If your dataset's transitional year between centuries differs, change the `yr>30` segment in the formula to reflect your data's cutoff date.
Select the cell containing your new formula, then click and drag the small square icon (fill handle) in the bottom-right corner down to the last row of your data.
Select the entire dataset, navigate to the 'Data' tab, click 'Sort', and choose the newly created helper column to perform an A to Z ascending sort.
Sort Chronological Codes Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced logical and text-manipulation formulas, including LET, making it easy to seamlessly solve complex sorting tasks involving alphanumeric dates.
- 1. Open Your Spreadsheet: Launch WPS Office and open the workbook containing your mixed-format date codes.
- 2. Apply the Conversion Formula: Create a new column and paste the LET string manipulation formula to construct four-digit sortable years.
- 3. Perform Document Sorting: Select the table range, click 'Sort' under the 'Data' tab, and set your parameter to sort ascending by the new formula-based helper column.
Frequently Asked Questions
Why does Excel sort '00' before '99' in formatted date codes?
When numbers are positioned inside text sequences or alphanumeric strings, spreadsheet software sorts them character by character alphabetically, not by their numeric value. Since '0' alphabetically precedes '9', '00' sorts before '99', regardless of the chronological century.
Can I use this method if my spreadsheet software does not support the LET function?
Yes. If your software version lacks the LET function, you can achieve the same result by nesting traditional text functions like LEFT, MID, FIND, and IF. However, utilizing modern office software like the latest version of WPS Office or Microsoft 365 allows you to use LET, which drastically simplifies understanding and editing the formula.
Will manipulating the text string alter my original cell data?
No. By using a helper column, your original alphanumeric code remains entirely unchanged. The helper column is strictly used as the reference point to execute the Sort command on the entire data range.
How do I change the century cutoff year in the formula?
Within the formula's IF statement logic `IF(yr>30,"19","20")`, the number 30 serves as the cutoff. You can replace `30` with any two-digit number that reflects the century transition logic specific to your business or data requirements.
