Function Problems

How to Sort Mixed Alphanumeric Part Numbers Correctly in Excel

WPS Content Manager Sep 30, 2026 868 views

Question details

The user needs a way to logically sort part numbers that contain both letters and numbers, bypassing the default text-based sorting behavior in Excel.

Product: Excel
Device & OS: not provided
Scenario: Organizing inventory, part lists, or databases where entries consist of mixed alphanumeric values like DM74S678 or 1N2902.
Observed behavior: Excel stores mixed values as text and sorts them character-by-character, which fails to produce a natural or desired alphanumeric order.

Before you start

Determine the exact sorting logic you need (e.g., prioritizing letters before numbers) and ensure you have at least two empty columns adjacent to your data to use as helper columns.

Solution 1Recommended

Use Helper Columns to Separate Text and Numbers

Extract the alphabetic and numeric portions into separate columns to gain full control over the multi-level sorting logic.

Because Excel evaluates alphanumeric strings purely as text, it sorts digit by digit from left to right (placing '10' before '2'). Separating the text and number components allows you to apply numeric sorting to the numbers and alphabetical sorting to the letters.

Create helper columns

Insert two new blank columns next to your list of part numbers. Name the first column 'Letters' and the second 'Numbers'.

Extract the data

Type the alphabetic portion of your first part number into the 'Letters' column and the numeric portion into the 'Numbers' column. Use Excel's Flash Fill feature (Ctrl + E) on both columns to automatically extract the rest of the data.

Apply multi-level sort

Select your entire dataset, including the new helper columns. Go to the Data tab and click on the 'Sort' button.

Configure sorting rules

In the Sort dialog box, add a primary sort level for the 'Letters' column (A to Z), click 'Add Level', and add a secondary sort for the 'Numbers' column (Smallest to Largest). Click OK.

Use Helper Columns to Separate Text and Numbers

Flash Fill Tip: If Flash Fill does not accurately recognize complex patterns, you can use formulas combining LEFT, RIGHT, MID, FIND, and VALUE to extract the data systematically.

Solution 2

Use Power Query to Split and Sort Data

Leverage Power Query for larger datasets to automatically detect and split columns by character transitions without manual formula creation.

Efficient Data Sorting in WPS Spreadsheet

Sort Complex Data Effortlessly with WPS Office

WPS Spreadsheet provides powerful built-in text manipulation tools, intelligent Flash Fill, and advanced custom sorting capabilities to help you organize complex alphanumeric part numbers accurately and quickly.

1. Open your data in WPS: Launch WPS Spreadsheet and open the workbook containing your mixed alphanumeric part numbers.
2. Use Flash Fill: Create empty adjacent columns. Manually type the text portion for the first row, then press Ctrl+E to automatically fill the rest. Repeat for the numeric portion.
3. Access Custom Sort: Highlight your entire data range. Navigate to the Data tab on the top ribbon and click the Sort icon.
4. Apply Multi-level Sorting: Set your primary sorting criteria to the extracted text column, then add a level to sort by the extracted numeric column. Click OK to instantly reorganize your list.

Completely free, lightweight, and fast performance for large datasets100% compatible with Microsoft Excel file formats (.xlsx and .xls)Intelligent Flash Fill feature to easily separate text and numbers without complex formulasIntuitive multi-level custom sorting dialog for precise data organization

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel sort alphanumeric data incorrectly?

When a cell contains a mix of letters and numbers, Excel treats the entire cell value as a text string. Text sorting compares strings character by character from left to right, which means '10' is evaluated as starting with '1' and is placed before '2'.

Can I sort alphanumeric strings without using helper columns?

Standard Excel sorting cannot inherently separate and recognize the numeric value inside a mixed text string without helper columns. While advanced VBA macros can sort data without visible helper columns, using helper columns remains the most reliable and accessible method.

How do I handle part numbers with varying string lengths?

If part numbers have inconsistent lengths, you can use the Power Query 'Split by Digit to Non-Digit' feature, or use dynamic string manipulation formulas (incorporating FIND, MIN, and SEARCH functions) to locate where the numeric value begins.

Will sorting by helper columns mess up my original data?

No, as long as you select the entire dataset (both original columns and helper columns) before clicking Sort. This ensures that the original part numbers move together with their corresponding extracted helper values.

Excel & Data Problems