Formatting Issues

How to Fix Excel Sort Warning for Numbers Stored as Text

Huma Ashraf Ch Oct 12, 2026 871 views

Question details

The user needs a way to bypass or resolve the sort warning in Excel that triggers when sorting data containing numbers formatted or stored as text.

How to Fix the Excel Sort Warning for Numbers Stored as Text

Product: Microsoft Excel
Device & OS: not provided
Scenario: Sorting a dataset that contains a mix of standard numeric values and numbers that have been formatted or stored as text.
Observed behavior: Excel displays a Sort Warning interrupting the sort action. Additionally, changing the cell format to 'Text' from the ribbon does not convert the pre-existing numeric values.

Before you start

Identify the mixed data types in your spreadsheet by looking for cells with a small green triangle in the top-left corner, which indicates a number is currently stored as text.

Solution 1Recommended

Select the Correct Option in the Sort Warning Dialog

Since Excel does not offer a setting to disable this specific warning permanently, the quickest workaround is to manage the behavior directly within the prompt.

When Excel detects a conflict between numbers and numbers stored as text during a sort operation, it asks you how to handle the discrepancy. Choosing the right option ensures your data is ordered exactly how you want it without needing complex conversions.

Trigger the sort action

Highlight the column or range of data you want to sort, navigate to the 'Data' tab, and click the 'Sort' button.

Choose the sorting behavior

When the 'Sort Warning' dialog box appears, select 'Sort anything that looks like a number, as a number' if you want all items grouped sequentially. Select 'Sort numbers and numbers stored as text separately' if you want them strictly segregated.

Apply the sort

Click 'OK' to execute the sorting operation based on your selected preference.

Select the Correct Option in the Sort Warning Dialog

No Permanent Disable Option: There is no built-in standard option in Excel to turn off this sort warning globally. You will need to make this selection each time the warning appears.

Solution 2

Convert Existing Numbers using Text to Columns

Use the Text to Columns feature to permanently convert existing numeric values into a true text format, as simply changing the cell format does not update existing data.

Solution 3

Convert Numbers to Text using the TEXT Function or Apostrophe

For individual cells or formula-driven sheets, you can force numbers to be treated as text by adding an apostrophe or using a dedicated formula.

Efficient Spreadsheet Management

Sort and Format Data Seamlessly with WPS Spreadsheet

WPS Office provides a highly compatible and intuitive Spreadsheet tool to handle data sorting, format conversions, and text processing without missing a beat.

1. Open your spreadsheet: Launch WPS Spreadsheet and open your existing .xlsx workbook.
2. Convert numeric formats: Highlight your target column, navigate to the Data tab, and use the Text to Columns feature to define the format as Text.
3. Sort without conflict: Click the Sort icon under the Data tab to organize your information effortlessly.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Easily manage data conversion with an intuitive Text to Columns toolAdvanced sorting features to seamlessly organize mixed data typesFree, lightweight, and easy-to-use Office alternative

microsoft office alternative - wps office

Frequently Asked Questions

Can I permanently disable the Excel sort warning for numbers stored as text?

No, Excel does not provide a built-in option to permanently turn off this specific sort warning. You must select your preferred sorting method in the dialog box each time it triggers.

Why didn't changing the cell format to 'Text' update my existing numbers?

Applying a 'Text' format from the Home tab only affects new data entered into those cells moving forward. It does not retroactively convert numbers that have already been typed into the cells.

How can I visually identify numbers stored as text in my sheet?

Excel flags numbers stored as text by placing a small green triangle in the top-left corner of the cell. If you select the cell, a yellow warning icon appears; clicking it will display the message 'Number Stored as Text'.

Does the apostrophe (') show up when I print the spreadsheet?

No, when you type an apostrophe before a number to convert it to text, it acts as a hidden formatting character. It will be visible in the formula bar, but it will not display in the cell itself or on printed documents.

Document & File Issues