How to Sort Excel Values by Last Two Digits and Assign Departments
Question details
The user needs to extract the final two digits from text strings in a dataset, sort the data based on these digits, and automatically assign the records to specific departments based on numeric ranges.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Categorizing and organizing data based on trailing numeric codes within text strings.
- Observed behavior
- The goal state is to accurately extract the last two digits using formulas and map them to department ranges using a lookup table, rather than relying on complex, nested IF statements.
Ensure your data is organized with clear columns for the original values, and that you have created a separate, dedicated lookup table defining the numeric boundaries for each department.
Extract Digits and Assign Departments Using VLOOKUP
Use a helper column to extract the last two digits as a number, then use a VLOOKUP formula with an approximate match to assign departments based on a lookup table.
Using nested IF formulas for multiple ranges can become difficult to manage and prone to errors. A much cleaner approach is to use a helper column to pull the last two digits and then run an approximate-match lookup against a dedicated range table.
In an empty column next to your data (for example, cell B4), enter the formula =VALUE(RIGHT(A4,2)) to extract the last two characters from cell A4 and convert them into a numeric value.
Create a separate table defining your department ranges. Put the lower boundary of each numeric range in the first column (this column must be sorted in ascending order) and the corresponding department name in the second column.
In a new column for the Department, enter a formula like =VLOOKUP(B4, $E$2:$F$10, 2, TRUE). Replace $E$2:$F$10 with the actual absolute reference of your lookup table. The TRUE argument is critical as it enables approximate matching.
Select your entire dataset, go to the 'Data' tab on the top ribbon, click 'Sort', and choose the helper column containing the extracted digits to organize your data.
Assign Departments Using XLOOKUP
If you are using a newer version of Excel or WPS Office, XLOOKUP provides a more robust and flexible way to perform approximate matches without requiring your lookup table to be strictly sorted.
Use WPS Spreadsheet to Easily Sort and Categorize Data
WPS Spreadsheet provides comprehensive support for advanced functions like RIGHT, VALUE, VLOOKUP, and XLOOKUP, allowing you to seamlessly process complex data, extract text, and assign categories with high efficiency.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the data you want to categorize.
- 2. Insert Extract Formula: Type =VALUE(RIGHT(A1,2)) in an empty helper cell to pull the trailing digits from your text string and convert them to numbers.
- 3. Apply Lookup Function: Use the built-in VLOOKUP or XLOOKUP function to match the extracted number against your department criteria table.
- 4. Sort the Results: Highlight the data range, navigate to the Data tab, and click Sort to quickly organize the rows by the extracted digits.
Frequently Asked Questions
Why does the RIGHT function return text instead of a number?
The RIGHT function is a text function, meaning it always returns a text string—even if the characters are digits (e.g., "14" instead of 14). Wrapping it in the VALUE function, like =VALUE(RIGHT(A1,2)), converts the extracted text into a proper numerical value so it can be successfully matched with numeric lookup tables.
Why is my VLOOKUP returning an error or the wrong department?
Ensure your lookup table is sorted in ascending order by the boundary column. For approximate matches (when using the TRUE argument in VLOOKUP), sorting the lookup array from smallest to largest is strictly required. Also, verify that both your extracted value and the lookup table contain the same data type (numbers).
Can I look up the departments without creating a helper column?
Yes, you can nest the extraction formula directly inside the lookup formula to skip the helper column. For example: =VLOOKUP(VALUE(RIGHT(A4,2)), $E$2:$F$10, 2, TRUE). However, keeping the helper column often makes it easier to visually verify the data and allows you to sort the entire dataset by those last two digits directly.
