Convert Excel Experience Text to Sortable Years and Months Values
Question details
Convert experience text such as "15years 6months" into consistent values that can be sorted by total years and months.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The worksheet contains experience durations written as text, and the user needs to sort the records in chronological experience order.
- Observed behavior
- Text entries do not provide reliable numeric sorting, and month values from 0 to 9 need leading zeros when displayed as a combined value.
Make sure every source cell follows the same pattern, such as 15years 6months, including a month value even when it is 0. Correct misspellings such as "yeras" before applying the formulas.
Extract numeric years and months into separate columns for accurate sorting
Use this method when you need a reliable sort order. Separate numeric columns prevent values such as 15.11 from being treated as a decimal number.
A combined value such as 15.06 is useful for display, but it should not be used as a mathematical decimal duration. Sort first by the numeric Years column and then by the numeric Months column.
Insert a column named Years. If the experience text is in C2, enter =--TRIM(LEFT(LOWER(C2),FIND("years",LOWER(C2))-1)) and press Enter.
Insert a column named Months. In the first row, enter =--TRIM(MID(LOWER(C2),FIND("years",LOWER(C2))+5,FIND("months",LOWER(C2))-FIND("years",LOWER(C2))-5)) and press Enter.
Select the two formula cells and drag the fill handle down, or double-click the fill handle to copy the formulas through the adjacent data range.
Select the full data range, then choose Data > Sort. Sort by Years from Smallest to Largest, click Add Level, and sort by Months from Smallest to Largest.
Create a padded years-and-months display value
Use this option when you also want a readable display such as 15.06 after extracting the numeric values.
Use Microsoft 365 TEXTSPLIT formulas to extract the values
Use this method when Microsoft 365 functions such as LET and TEXTSPLIT are available and every entry has a space between the years and months portions.
Split and sort experience durations in WPS Spreadsheet
WPS Spreadsheet can extract the year and month portions of experience text into numeric columns, then sort the complete table using a multi-level sort. You can also create a padded display value such as 15.06 without losing accurate numeric sorting.
- 1. Open the worksheet in WPS Spreadsheet: Open the Excel file in WPS Office and select the worksheet containing the experience text column.
- 2. Create numeric extraction columns: Insert columns beside the experience text and label them Years and Months.
- 3. Enter the year and month formulas: Use formula cells to extract the text before "years" and the text between "years" and "months", then fill the formulas down.
- 4. Sort the full data range: Select the table, choose Data > Sort, and add Years as the first sort key and Months as the second sort key.
Frequently Asked Questions
Why should I not sort by a value like 15.06?
A value like 15.06 is a display format, not a true decimal-year value. For reliable results, use Data > Sort with separate numeric Years and Months columns.
How can I show one-digit months with a leading zero?
After extracting numeric values, use =D2&"."&TEXT(E2,"00"), where D2 contains years and E2 contains months. The TEXT function changes 6 to 06.
Can the formula handle 0years 6months?
Yes. If the entry follows the same spelling and structure, the extraction formulas return 0 for Years and 6 for Months, allowing it to sort before 1years 0months.
What if some cells say "15 years 6 months" with spaces?
Standardize the source format first, or adjust the formulas for the spaces. You can use Find and Replace to replace " years" with "years" and " months" with "months" if needed.
