Formatting Issues

How to Fix Excel Number Sequencing and Sorting for Text Values

Rana GarciaRana Garcia Nov 10, 2026 870 views

Question details

The user needs to correctly sequence and sort alphanumeric text strings (such as S25.001 and S25.1000) numerically instead of alphabetically.

How to Fix Excel Number Sequencing and Sorting for Alphanumeric Text Values
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 you start

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.

Solution 1Recommended

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.

1
Insert a helper column

Insert a new blank column immediately next to your text values to act as the helper column for sorting.

2
Use the extraction formula

For newer versions, enter the formula =--TEXTAFTER(A1,".") in the first cell to extract numbers after the decimal point.

3
Alternative formula for older versions

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).

4
Apply down the column

Drag the fill handle from the corner of the cell to apply the formula down the rest of your helper column.

5
Sort by the 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.

Extract the Numeric Value Using a Helper Column
Formula Tip: The double unary operator (--) at the beginning of the formulas converts the extracted text string back into a true numeric value, ensuring it sorts sequentially.
Powerful Data Processing

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. 1. Open your data: Open your workbook containing the alphanumeric sequence data in WPS Spreadsheet.
  2. 2. Extract values: Insert a helper column and use the MID or TEXTAFTER formula to extract the numeric values from your text strings.
  3. 3. Sort the dataset: Select your entire data range, navigate to the 'Data' tab, and click on 'Sort'.
  4. 4. Apply sorting rules: Choose your helper column as the primary sorting key, select ascending order, and click 'OK'.
100% compatible with Microsoft Excel formulas and formatting rulesAdvanced sorting options to organize complex data efficientlyFree and lightweight alternative for all your everyday spreadsheet tasks
microsoft office alternative - wps office

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.