Sorting Mixed Data Types in Excel Using ISNUMBER and ISTEXT

Sorting Excel columns containing a chaotic mix of numbers and text often disrupts logical data presentation, presenting a frustrating challenge for analysts. While standard A-Z filters or basic sorting tools fail to categorize these mixed data types cleanly, integrating logical functions provides a sophisticated workaround. This methodology grants users absolute control over how data is prioritized. However, this approach stipulates using dynamic arrays to evaluate cell content. For instance, combining SORTBY with ISNUMBER and ISTEXT cleanly isolates numerical values from text strings. Below, we detail how to implement this formula for flawless data organization.

Understanding the Challenge of Mixed Data Types in Excel

Excel is an incredibly versatile tool, but it can behave in unexpected ways when dealing with datasets that contain mixed data types. A common scenario that frustrates spreadsheet users is trying to sort a column containing both numeric values and text strings. By default, Excel's standard sort tool organizes numbers first, followed by text (in ascending order). However, this default behavior is often rigid and does not fit specific reporting or analytical requirements.

For instance, you might need to display all text entries alphabetically at the top of your sheet, followed by all numeric entries in ascending order. Alternatively, you might want to isolate numeric values that have been mistakenly formatted as text. To achieve a custom, dynamic sort order without manually separating your data, you can harness the power of modern dynamic array formulas-specifically combining SORTBY with the logical functions ISNUMBER and ISTEXT.

Why Standard Sorting Falls Short

When you use the default "Sort A to Z" or "Sort Z to A" buttons in Excel, the application applies a built-in hierarchy:

• Numbers (including dates and times) are sorted first.
• Text values (including alphanumeric strings and numbers formatted as text) are sorted next.
• Logical values (TRUE/FALSE) follow text.
• Error values are grouped together.
• Blank cells are always placed at the bottom, regardless of the sort order.

This rigid sequence is problematic if your layout demands a different structure. Furthermore, standard sorting is a static operation; if your underlying data changes, you must manually re-apply the sort. By using formulas, you can build a dynamic sorting engine that automatically updates whenever your source data changes.

The Core Logic: How ISNUMBER and ISTEXT Help

To bend Excel's sorting behavior to your will, you need to provide the sorting engine with a custom set of "weights" or priorities. This is where ISNUMBER and ISTEXT come into play.

These logical functions evaluate a cell and return either TRUE or FALSE:

• =ISNUMBER(value): Returns TRUE if the value is a number; otherwise, it returns FALSE.
• =ISTEXT(value): Returns TRUE if the value is text; otherwise, it returns FALSE.

When these functions are applied to an entire range within a formula, they generate an array of TRUE and FALSE values. When Excel performs mathematical operations or sorting routines on logical values, it treats TRUE as 1 and FALSE as 0. By utilizing this binary coercion, we can construct custom sorting keys to group our data exactly how we want.

Scenario 1: Sorting Text First, Then Numbers

Let's look at a practical example. Suppose you have the following mixed list in range A2:A9:

If you want to sort this list so that all text entries appear first alphabetically, followed by all numbers in ascending order, you can use the following SORTBY formula:

=SORTBY(A2:A9, ISTEXT(A2:A9), -1, A2:A9, 1)

How This Formula Works Step-by-Step

1. ISTEXT(A2:A9): This is our first sorting criteria. Excel evaluates each cell in the range and creates an internal array of logical values: {TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}.
2. Coercion & Descending Sort (-1): Because we specified a sort order of -1 (descending) for our first criteria, Excel sorts the coerced 1s (TRUE) before the 0s (FALSE). This cleanly splits our dataset, pushing all text entries to the top and all numeric entries to the bottom.
3. A2:A9 (Second Sort Criteria): Within the text group (where the first key was TRUE) and the numeric group (where the first key was FALSE), Excel applies the second sorting rule. We reference the original range and set the sort order to 1 (ascending).
4. The Final Output: Excel resolves the alphabetical sort for the text values, and then resolves the numerical sort for the numbers, yielding:

Scenario 2: Sorting Numbers First, Then Text

If your goal is the reverse-putting all numbers first in ascending order, followed by all text alphabetically-you simply shift your logical check. Instead of checking for text, you check for numbers using ISNUMBER.

Write the following formula in your target cell:

=SORTBY(A2:A9, ISNUMBER(A2:A9), -1, A2:A9, 1)

Behind the Scenes

The ISNUMBER(A2:A9) array evaluates to {FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE}. Sorting this array in descending order (-1) forces the TRUE values (numbers) to the top of the queue. The secondary sort criteria (A2:A9, 1) then takes over to ensure the numbers are listed from smallest to largest, and the text items are listed from A to Z immediately after.

Handling Advanced Scenarios: Numbers Formatted as Text

In real-world spreadsheets, you often encounter "dirty" data, such as numbers that have been formatted or imported as text. A cell containing '150 (with a leading apostrophe) or imported from an external database as a text string will fail the ISNUMBER test and pass the ISTEXT test.

If you sort these mixed types without cleaning them, Excel will treat "150" (text) differently than 150 (number). To normalize this behavior, you can combine your sorting formula with the VALUE function or the double unary operator (--) to temporarily convert numeric text strings into actual numbers during the sorting process.

To sort a list where some numbers are stored as text, but you want them treated as true numbers during the sort, use this nested structure:

=SORTBY(A2:A9, ISNUMBER(VALUE(A2:A9)), -1, A2:A9, 1)

Note: If your list contains true alphabetic text (like "Apple"), the VALUE function will return a #VALUE! error for those cells. To prevent your sorting engine from breaking, wrap it in an error-handling function like IFERROR:

=SORTBY(A2:A9, IFERROR(ISNUMBER(VALUE(A2:A9)*1), FALSE), -1, A2:A9, 1)

Making Formulas Cleaner with the LET Function

If you are working in Microsoft 365 or Excel 2021, you can use the LET function to define variables. This makes your formulas much easier to read, debug, and maintain, especially when dealing with complex ranges or multiple criteria.

Here is how you can write the "Text First" sorting formula using LET:

=LET(
    source_data, A2:A9,
    is_text_array, ISTEXT(source_data),
    SORTBY(source_data, is_text_array, -1, source_data, 1)
)

By defining source_data at the beginning, you only have to specify your range (A2:A9) once. If your data expands to A2:A500 in the future, you only need to update the formula in a single location.

Alternative for Older Excel Versions (Legacy Array Formulas)

The SORTBY function is a dynamic array function available only in modern Excel versions. If you are using an older version of Excel (such as Excel 2016 or 2013), you cannot use these spill formulas. Instead, you must use a helper column approach:

1. In an adjacent column (e.g., Column B), create a helper key formula:
   =IF(ISTEXT(A2), 1, 2)
2. Copy this formula down alongside your dataset.
3. Select your entire table (Columns A and B).
4. Go to the Data tab, click Sort, and add two levels of sorting:
   • First level: Sort by Column B (Ascending)
   • Second level: Sort by Column A (Ascending)

While this legacy method is manual and requires extra columns, it achieves the exact same structural output as our modern dynamic array formulas.

Summary

Sorting mixed data types doesn't have to result in messy, disjointed sheets. By leveraging SORTBY alongside ISNUMBER and ISTEXT, you gain complete control over your spreadsheet's visual hierarchy. Whether you are generating inventory reports, preparing raw data imports for analysis, or simply organizing user lists, these advanced formulas ensure your data remains perfectly ordered and fully dynamic.