Data Import & Export

How to Fix Excel Sorting Numbers as Text Instead of Numerically

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to correct a sorting issue in Excel where numbers are being sorted alphabetically (e.g., 1, 10, 11, 2) because they are formatted as text or include additional characters.

Product: Excel / Spreadsheet
Device & OS: not provided
Scenario: Sorting numeric data that has been formatted as text or contains mixed alphanumeric characters.
Observed behavior: Numbers are sorted alphabetically (1, 10, 11, 2) rather than in correct numerical order (1, 2, 10, 11).

Before you start

Highlight the column containing the problematic numbers to check if a green error triangle appears in the top-left corner of the cells, which confirms the numbers are currently stored as text.

Solution 1Recommended

Convert Text to Numbers and Sort

Use this method when pure numbers are mistakenly formatted as text, causing alphabetical sorting.

When Excel treats numbers as text, it sorts them based on the first digit rather than the actual value. Converting them back to a number format instantly resolves this.

Select the data

Highlight the cells containing the numbers that are sorting incorrectly.

Convert to Number

Click the warning icon (a yellow diamond with an exclamation mark) that appears next to the selected cells, then choose 'Convert to Number' from the dropdown menu.

Sort the data

Go to the 'Data' tab and click 'Sort' to arrange the numbers in ascending or descending numerical order.

Alternative Conversion Method: You can also use the 'Text to Columns' feature under the Data tab and simply click 'Finish' to quickly convert a whole column of text numbers into real numbers.

Solution 2

Extract Numbers from Mixed Text Strings for Sorting

Use this method if the cells contain numbers followed by text (e.g., '12 apples', '2 bananas').

Efficient Data Management

Easily Convert and Sort Data with WPS Spreadsheet

WPS Spreadsheet provides intuitive tools to instantly convert numbers stored as text and sort your data flawlessly. It is a highly capable and free suite, offering robust data processing tools perfectly tailored for daily office tasks.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your spreadsheet containing the incorrectly sorted data.
2. Highlight and convert: Select the problematic cells, click the yellow error alert icon beside them, and select 'Convert to Number'.
3. Apply numerical sort: Navigate to the Data tab and click the Sort icon to properly order your numeric values from smallest to largest.

One-click error checking to convert numbers stored as text.Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formats.Advanced custom sorting options for complex alphanumeric data.Free and lightweight alternative to heavy office suites.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel sort 10 before 2?

When numbers are stored as text, Excel sorts them character by character from left to right. Since the character '1' comes before '2' in alphabetical order, any number starting with '1' (like 10, 11, 100) will be placed before '2'.

Can I force a column to format as numbers before typing?

Yes. Select the column, right-click and choose 'Format Cells'. Under the Number tab, select 'Number' instead of 'Text' or 'General', then click OK to ensure newly entered data is treated numerically.

How do I sort alphanumeric data like 'Item 1', 'Item 2', 'Item 10'?

To sort these correctly, you either need to add leading zeros (e.g., 'Item 01', 'Item 02', 'Item 10') so the text sorting logic aligns with numerical logic, or extract the number into a separate column and apply a numerical sort to that helper column.

Excel & Data Problems