Function Problems

How to Sort Excel IDs with Letters, Periods, and Numbers Correctly

Huda Qurayshi Sep 28, 2026 871 views

Question details

The user needs a way to accurately sort alphanumeric IDs containing letter prefixes, periods, and numbers, avoiding the default text sorting order.

How to Sort Mixed IDs with Letters, Periods, and Numbers in Excel

Product: Excel
Device & OS: not provided
Scenario: Organizing a dataset where IDs are complex combinations of text and numbers (e.g., WD4.01, WD4.10) that need to be sorted in proper numeric sequence.
Observed behavior: Excel treats mixed IDs as text, resulting in incorrect lexicographical sorting where numbers like 1, 11, 2, and 20 are placed in the wrong logical order.

Before you start

Ensure your dataset does not contain hidden trailing spaces, and always select your entire data table (not just the ID column) before sorting to prevent row data misalignment.

Solution 1Recommended

Use Helper Columns with Extraction Functions

Split the mixed ID into distinct prefix and numeric components using text functions, allowing Excel to evaluate and sort the numbers correctly.

Because Excel reads mixed formats as text, breaking the ID down into separate columns isolates the numeric values.

By converting extracted text strings into actual numbers, Excel's sorting engine will correctly place '2' before '10'.

Insert helper columns

Right-click the column letter next to your IDs and insert three new blank columns for the prefix, major number, and minor number.

Extract the letter prefix

In the first helper column, use the LEFT function to extract the prefix letters. For example, if the prefix is always two letters, enter =LEFT(A2, 2).

Extract the numeric parts

Use functions like TEXTBEFORE and TEXTAFTER (or TEXTSPLIT) to extract the numbers before and after the period. Multiply the result by 1 to convert it to a real number, for example: =TEXTAFTER(A2, ".")*1.

Apply Custom Sort

Select your entire data range. Go to the Data tab, click Sort, and add three sorting levels: first by the Prefix column, then by the Major Number column, and finally by the Minor Number column.

Use Helper Columns with Extraction Functions

Pro Tip: Multiplying a text-formatted number by 1 (or adding 0) is a quick and effective way to force Excel to recognize the string as a numeric value for accurate sorting.

Solution 2

Standardize Numeric Sections with Leading Zeros

Format the numeric parts of the ID to have a uniform length using leading zeros, which allows Excel's default text sorting to work perfectly.

Efficient Data Sorting

Sort Complex Data Easily with WPS Spreadsheet

WPS Spreadsheet offers powerful data handling and calculation capabilities, fully supporting advanced text extraction formulas to help you process and sort complex alphanumeric datasets accurately.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office, open Spreadsheet, and load the workbook containing your mixed IDs.
2. Set up helper columns: Add blank columns next to your data and input your text extraction formulas to separate the letters and numbers.
3. Access the sorting tool: Highlight your entire data table, navigate to the Data tab on the top ribbon, and click the 'Sort' button.
4. Apply multi-level custom sorting: In the Custom Sort dialog box, add sorting levels for your newly extracted prefix and numeric columns, then click OK to reorder your data.

Fully compatible with Microsoft Excel file formats (.xlsx, .xls, .csv)Supports all major text extraction formulas like LEFT, TEXTBEFORE, and TEXTSPLITLightweight software with rapid startup and a familiar, user-friendly interfaceAdvanced multi-level sorting features to handle complex helper columns effortlessly

Frequently Asked Questions

Why does Excel sort 10 before 2 in my ID column?

When numbers are mixed with text (like letters or multiple periods), Excel treats the entire cell as a text string. Text sorting evaluates characters sequentially from left to right. Since the character '1' comes before '2', Excel places '10' ahead of '2'.

What happens if I only select the ID column when sorting?

If you only highlight the ID column, Excel will only rearrange those specific cells. The rest of your row data will stay in its original position, causing mismatched records and corrupting your dataset. Always select the entire range before initiating a sort.

Can I sort alphanumeric data without creating helper columns?

Generally, helper columns are the most reliable method for complex mixed IDs. However, if your data follows a very strict and limited pattern, you might be able to create a Custom Sort List. For larger, dynamic datasets, helper columns or Power Query are required.

How do I ensure extracted text numbers are treated as actual numbers?

Text extraction formulas return text strings even if they look like numbers. You can convert them to actual numbers by performing a math operation that doesn't change the value, such as multiplying the function's result by 1 (e.g., =RIGHT(A2, 2)*1) or adding zero (+0).

Excel & Data Problems