How to Fix Excel Number Sequencing and Sorting for Text Values
Question details
The user needs to correctly sequence and sort alphanumeric text strings (such as S25.001 and S25.1000) numerically instead of alphabetically.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Sorting alphanumeric codes, part numbers, or formatted sequences that contain both text prefixes and numeric values.
- Observed behavior
- The application sorts the text-based numbers alphabetically, placing S25.1000 before S25.001, instead of following the correct numeric sequence.
Before applying these sorting fixes, identify the specific pattern of your text values (such as prefix length or delimiters) and decide whether you want to alter the original data format or use a secondary column to assist with the sorting process.
Extract the Numeric Value Using a Helper Column
The most reliable way to sort text values that contain numbers is to extract the numeric portion into a separate helper column and sort your data by that column.
This method leaves your original data intact while allowing the spreadsheet to read the sequential numbers algebraically.
You can use different formulas depending on your software version to pull out the digits following the period or specific characters.
Insert a new blank column immediately next to your text values to act as the helper column for sorting.
For newer versions, enter the formula =--TEXTAFTER(A1,".") in the first cell to extract numbers after the decimal point.
If you are using an older version, use the MID function: =--MID(A1,5,99) (adjusting the '5' based on where your numeric sequence begins).
Drag the fill handle from the corner of the cell to apply the formula down the rest of your helper column.
Select your entire dataset, go to the 'Data' tab, click 'Sort', and choose to sort your data using the values in the new helper column.

Store as Numbers and Apply Custom Formatting
If you only need to display a text prefix but sort numerically, you can store the base numbers as numeric values and apply a custom format.
Standardize Text Length with Consistent Leading Zeros
If you must keep the values strictly as text strings without using helper columns, padding the numbers with leading zeros ensures alphabetical sorting aligns with numeric sorting.
Sort Complex Alphanumeric Data Easily in WPS Spreadsheet
WPS Spreadsheet provides powerful data manipulation tools, including helper column formulas and advanced custom number formatting, to help you sort complex alphanumeric text strings perfectly.
- 1. Open your data: Open your workbook containing the alphanumeric sequence data in WPS Spreadsheet.
- 2. Extract values: Insert a helper column and use the MID or TEXTAFTER formula to extract the numeric values from your text strings.
- 3. Sort the dataset: Select your entire data range, navigate to the 'Data' tab, and click on 'Sort'.
- 4. Apply sorting rules: Choose your helper column as the primary sorting key, select ascending order, and click 'OK'.

Frequently Asked Questions
Why does Excel sort my numbers like 1, 10, 2 instead of 1, 2, 10?
Excel sorts values formatted as text character by character from left to right. Since the character '1' in '10' comes before '2', Excel places 10 before 2. Converting the text to true numbers or adding leading zeros fixes this behavior.
How do I add leading zeros to my numbers in Excel?
You can add leading zeros by right-clicking the cell, selecting 'Format Cells', choosing 'Custom', and typing zeros corresponding to the desired number of digits (e.g., typing '0000' forces a number like 25 to display as '0025').
Will the TEXTAFTER function work in all Excel versions?
No, the TEXTAFTER function is only available in Excel 365 and Excel for the web. For older versions, you should use combinations of MID, LEFT, RIGHT, or FIND functions to extract the numeric portion of a text string.




