How to Sort Excel Values Like ABC-1000 and ABC-10000 Correctly in WPS Office Spreadsheets
Question details
The user needs to sort values such as ABC-1000 and ABC-10000 by their numeric suffix instead of the spreadsheet's default text sort order.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- User is working with a column that contains text prefixes followed by numeric suffixes, such as ABC-1000 and ABC-10000, and wants a true numeric sort.
- Observed behavior
- Spreadsheet sorts the values as text, placing ABC-10000 in an unexpected position rather than sorting by the numeric suffix after the hyphen.
Check that your values follow a consistent pattern with a hyphen separating the text prefix and numeric suffix, and insert two empty helper columns beside the column you need to sort.
Sort a mixed text-number list by its numeric suffix using helper columns
Use this method when normal sorting places ABC-10000 in the wrong position because the cell content is treated as text instead of a number.
The first helper column extracts the text before the hyphen. The second helper column removes the hyphen and converts the suffix to a real number, so the Sort dialog can apply numeric sorting.
After sorting by these helper columns, the original values are reordered without changing their cell content.
Select the two columns to the right of the column that contains the values. If the values are in column D, use columns E and F as helper columns.
Click E2, enter =LEFT(D2,FIND("-",D2)-1), and press Enter. This returns the text before the hyphen.
Click F2, enter =--MID(D2,FIND("-",D2)+1,100), and press Enter. The double minus converts the extracted suffix into a real number.
Select E2:F2 and drag the fill handle down through the last row of your data, or double-click the fill handle to fill automatically.
Select the entire data range including the helper columns. Go to Data > Sort, set the first sort key to column E for the prefix and add a second sort key to column F for the numeric suffix. Choose Ascending for both and confirm.
Sort mixed text-number values correctly in WPS Office Spreadsheets
Use two helper columns to separate text prefixes from numeric suffixes in WPS Office Spreadsheets, then sort by the real numeric value so codes such as ABC-1000 and ABC-10000 appear in the correct order.
- 1. Open your workbook and insert helper columns: Open your workbook in WPS Office Spreadsheets and insert two blank columns next to the column you need to sort.
- 2. Extract the prefix: In the first helper column, enter =LEFT(D2,FIND("-",D2)-1) to extract the text prefix.
- 3. Extract the numeric suffix as a number: In the second helper column, enter =--MID(D2,FIND("-",D2)+1,100) to extract the numeric suffix as a real number.
- 4. Open the Sort dialog: Fill the formulas down, select the complete range, and go to Data > Sort.
- 5. Apply the sort keys: Set the prefix column and numeric helper column as sort keys, choose Ascending, and confirm to apply the corrected order.
Frequently Asked Questions
Why does WPS Spreadsheets sort ABC-10000 in the wrong order?
When values contain text characters, the spreadsheet treats the whole cell as text and compares characters from left to right instead of comparing the number after the hyphen. This makes ABC-10000 sort differently than it would as a number.
What do the LEFT and MID helper formulas do?
The LEFT/FIND formula extracts the text before the hyphen. The MID/FIND formula extracts everything after the hyphen, and the double minus converts that extracted suffix from text to a real number so the Sort command can use numeric order.
Can I sort mixed text-number values in descending order?
Yes. Select the same range, open Data > Sort, keep the prefix column as the first sort key and the numeric helper column as the second sort key, then choose Descending for the numeric column.
Do the helper columns need to remain in the worksheet?
You can keep them and hide them if you want to re-sort later. If you delete the helper columns, the existing order remains, but you will need to recreate them before applying another numeric sort.
Will this method work if prefixes or suffixes have different lengths?
Yes. The formulas locate the hyphen with FIND, so they work with any prefix length before the first hyphen and any numeric suffix length after it, as long as each cell contains one hyphen.
