How to Sort Text And Numbers With Formula in Excel

Why This Task Matters in Excel

In any data-driven workplace you will eventually confront a column that mixes words and numbers. Product files often list numeric SKUs next to alphanumeric legacy codes, customer lists may combine ID numbers with “TBD” placeholders, and financial models sometimes pull both text labels and numeric subtotals into the same range. If you need a clean ascending or descending order, Excel’s built-in Sort command works fine—until the list must remain live. The moment new records land through data entry, Power Query refresh, or system integration, a manual sort becomes a bottleneck and a potential source of error.

Dynamic formula-based sorting eliminates that risk. Whether you are preparing a dashboard that refreshes every hour, building an automated quote generator that reshuffles price tiers, or simply creating a user-friendly lookup table for colleagues, a formula-driven sort maintains order without constant clicks. Being able to sort text and numbers together is especially important because Excel treats them differently—numbers use mathematical order whereas text follows the Unicode/ASCII sequence. If you do nothing, “10” will usually appear before “2” in a text sort, while pure numeric sorts ignore “N/A” or “Closed” entirely. Understanding how to bridge that gap preserves analytical integrity.

Industries from retail to healthcare rely on mixed columns: think of shelf labels such as “A12” and “B2”, pathology reports mixing counts with “Not Detected”, or shipping manifests balancing pallet counts with “Damaged” indicators. Mastering formula-based sorting therefore feeds directly into data cleansing, validation, and reporting workflows. It also dovetails with other dynamic-array skills such as FILTER, UNIQUE, and SEQUENCE. In modern Excel you can spill an entire sorted range with one formula and link that spill to charts, pivot tables, or data validation lists. Failing to learn this technique can yield misaligned lookups, broken chart labels, and embarrassing report errors. For professionals who automate in VBA, Python, or Power Query, knowing the underlying worksheet formula logic provides a reliable audit trail that non-programmers can follow.

Best Excel Approach

The most flexible modern solution is the combination of SORT and SORTBY. SORT can order by row or column; SORTBY lets you rank one array based on another. By coupling SORTBY with the ISNUMBER function you can push numbers to the top (or bottom) and then apply a secondary alphabetical or numeric order. This hybrid handles every common requirement:

• Treat numeric values as smaller than text, producing the familiar 1-2-3-A-B-C order.
• Reverse the logic so that text labels appear first.
• Provide one dynamic spill formula that automatically expands when new rows appear.

You should use this method when all stakeholders work in Microsoft 365 or Excel 2021+, because dynamic arrays spill automatically. If your organisation still uses Excel 2016 or earlier, you can replicate the result with helper columns and INDEX/SMALL constructions, but they are less elegant and slower.

Logic overview:

1. Build a key that ranks numbers before text using --ISNUMBER(value) or IF(ISNUMBER(value),0,1).
2. Combine that key with the original list in SORTBY.
3. Optionally add a second level of sort—ascending numbers and then alphabetical text.

Recommended syntax:

=SORTBY(source_range, --ISNUMBER(source_range), 1, source_range, 1)

• source_range = list that contains both text and numbers
• --ISNUMBER(...) converts TRUE/FALSE to 1/0, so numbers (1) or (0) can be placed first
• The 1 after each sort key means ascending order; use -1 for descending.

Alternative if you only need pure ascending without separating types:

=SORT(source_range, , 1, TRUE)

The optional comparison function parameter (TRUE) forces a natural sort that aligns text-numbers such as “A2”, “A10”, “A11” correctly. Older versions lack that feature, so you would fall back on helper columns.

Parameters and Inputs

• Required input: [A2:A100] (or any contiguous vertical range). The data type can be General, Text, or Number—Excel will coerce as needed.
• Optional keys: additional arrays of equal height that dictate secondary, tertiary, and further sort levels.
• Sort order flag: 1 for ascending, -1 for descending.
• Comparison logic (only with SORT): match_mode accepts 1 (exact), -1 (approx), or 2 (wildcard). Natural text-number ordering happens when you set ignore_case in some beta builds; for compatibility we rely on the numeric key method described above.

Data preparation:

1. Remove trailing spaces with TRIM or CLEAN if the list is imported.
2. Ensure numbers stored as text (green triangle) are intentionally left that way; otherwise convert with VALUE or multiply by 1.
3. Remove blank rows if you want blanks at the bottom; otherwise leave them and treat blanks as text.

Edge cases:

• Error values (#N/A, #VALUE!) propagate through the sort; wrap source in IFERROR if you want to push them to the bottom.
• Mixed units like “5kg” behave as text; strip units before sorting if you need numeric order.
• Scientific notation strings (“1.23E+5”) are treated as text unless you coerce to number.

Step-by-Step Examples

Example 1: Basic Scenario

Assume [B3:B12] holds the unsorted list:

B3: 40
B4: South
B5: 17
B6: Alpha
B7: 3
B8: Beta
B9: 25
B10: North
B11: 1
B12: Central

Goal: Ascending numbers first, then text A-Z.

Step 1 – Select an output cell, say D3.
Step 2 – Enter:

=SORTBY(B3:B12, --ISNUMBER(B3:B12), 1, B3:B12, 1)

Explanation:

• ISNUMBER returns TRUE for 40,17,3,25,1 and FALSE for the text items.
• The double unary (--) converts TRUE to 1 and FALSE to 0. Sorting by that column ascending puts 0s (numbers) first.
• The second key B3:B12 sorts numbers and text internally (1,3,17,25,40 then Alpha,Beta,Central,North,South).
  Result spills automatically in D3:D12.

Common variations:

• Descend by appending -1 after a key.
• Push text first by flipping the first sort order to -1.

Troubleshooting: If numbers stored as text don’t appear with numbers, wrap the range in VALUE inside the first key.

Example 2: Real-World Application

Scenario: A procurement team maintains an inventory list in [A2:A50]. Items are either numeric part numbers (e.g., 10234) or special-order codes (e.g., “XSP-45”). They need a live, sorted dropdown for data validation so warehouse staff can pick items quickly.

Data setup:

• A2:A50 mixed list.
• Data validation dropdown should point to a named spill range.

Step-by-step:

1. Create a named range called InvList referring to [A2:A50].
2. In a helper sheet, cell B2, enter the dynamic sort formula:

=SORTBY(InvList, --ISNUMBER(InvList), 1, InvList, 1)

3. Excel spills the sorted list downwards.
4. Define another name InvSorted as =B2#. The # symbol references the entire spill.
5. Back in the warehouse entry sheet, set data validation > List > =InvSorted.

Business impact: Whenever procurement appends new rows to [A2:A50], the spill range grows and the dropdown instantly reflects the updated ordering. That removes manual resorting and minimises data entry errors.

Integration: This named spill can feed pivot tables, XLOOKUP arrays, or be concatenated into comma-separated lists for ERP exports. Performance is excellent because dynamic arrays recalculate only when source cells change, not on every volatile event.

Example 3: Advanced Technique

Advanced need: You receive a monthly CSV of sales codes mixing pure numbers, text placeholders like “VOID”, and combined strings such as “A-102”. Management wants:

1. Pure numbers ascending
2. Alpha-numeric codes such as “A-102”, “B-7” sorted naturally (A before B, 7 before 102)
3. Text placeholders at the bottom

Solution uses three helper sort keys:

• Key 1: Type ranking (Number=1, AlphaNumeric=2, Text=3)
• Key 2: Letter component of AlphaNumeric (LEFT function)
• Key 3: Numeric component extracted with VALUE and MID

Formula in D2 for [C2:C500] source:

=SORTBY(
   C2:C500,
   IF(ISNUMBER(C2:C500),1, IF(ISNUMBER(VALUE(MID(C2:C500,2,LEN(C2:C500)))),2,3) ), 1,
   LEFT(C2:C500,1), 1,
   IFERROR(VALUE(MID(C2:C500,2,LEN(C2:C500))),9E+307), 1
)

Explanation:

• The nested IF assigns a type ranking: pure numbers 1, alphanumeric 2, everything else 3.
• Secondary sort by first letter manages “A-” family before “B-”.
• Tertiary numeric extraction orders “A-7” before “A-102”.
  Edge case handling: IFERROR(...,9E+307) pushes non-numeric strings to the end within their letter group.

Performance: Even with 10,000 rows this formula recalculates in under a second on modern hardware because each function is vectorised. For legacy builds, split keys into explicit columns to avoid repetitive calculations.

Tips and Best Practices

1. Use named ranges for your source data so formulas remain readable and resilient to resize operations.
2. Anchor ranges that may grow by converting them to Excel Tables; references like Table1[Code] automatically expand.
3. Minimise volatile functions (e.g., TODAY, RAND) inside sort keys—volatility forces recalculation even when data has not changed.
4. Cache complex helper keys in hidden columns if performance lags; then point SORTBY to those columns instead of recalculating inside the function.
5. Combine with FILTER to produce interactive views such as “Top 20 numeric codes” by wrapping the sorted spill inside INDEX or TAKE.
6. Document logic with comments because multi-level SORTBY formulas can look intimidating; a quick note prevents future confusion.

Common Mistakes to Avoid

1. Assuming numbers stored as text will sort numerically. Text “100” sorts after “9”. Fix by coercing with VALUE or multiplying by 1 inside the numeric key.
2. Forgetting to lock absolute references when copying formulas manually. Use structured references or `

How to Sort Text And Numbers With Formula in Excel

Why This Task Matters in Excel

In any data-driven workplace you will eventually confront a column that mixes words and numbers. Product files often list numeric SKUs next to alphanumeric legacy codes, customer lists may combine ID numbers with “TBD” placeholders, and financial models sometimes pull both text labels and numeric subtotals into the same range. If you need a clean ascending or descending order, Excel’s built-in Sort command works fine—until the list must remain live. The moment new records land through data entry, Power Query refresh, or system integration, a manual sort becomes a bottleneck and a potential source of error.

Dynamic formula-based sorting eliminates that risk. Whether you are preparing a dashboard that refreshes every hour, building an automated quote generator that reshuffles price tiers, or simply creating a user-friendly lookup table for colleagues, a formula-driven sort maintains order without constant clicks. Being able to sort text and numbers together is especially important because Excel treats them differently—numbers use mathematical order whereas text follows the Unicode/ASCII sequence. If you do nothing, “10” will usually appear before “2” in a text sort, while pure numeric sorts ignore “N/A” or “Closed” entirely. Understanding how to bridge that gap preserves analytical integrity.

Industries from retail to healthcare rely on mixed columns: think of shelf labels such as “A12” and “B2”, pathology reports mixing counts with “Not Detected”, or shipping manifests balancing pallet counts with “Damaged” indicators. Mastering formula-based sorting therefore feeds directly into data cleansing, validation, and reporting workflows. It also dovetails with other dynamic-array skills such as FILTER, UNIQUE, and SEQUENCE. In modern Excel you can spill an entire sorted range with one formula and link that spill to charts, pivot tables, or data validation lists. Failing to learn this technique can yield misaligned lookups, broken chart labels, and embarrassing report errors. For professionals who automate in VBA, Python, or Power Query, knowing the underlying worksheet formula logic provides a reliable audit trail that non-programmers can follow.

Best Excel Approach

The most flexible modern solution is the combination of SORT and SORTBY. SORT can order by row or column; SORTBY lets you rank one array based on another. By coupling SORTBY with the ISNUMBER function you can push numbers to the top (or bottom) and then apply a secondary alphabetical or numeric order. This hybrid handles every common requirement:

• Treat numeric values as smaller than text, producing the familiar 1-2-3-A-B-C order.
• Reverse the logic so that text labels appear first.
• Provide one dynamic spill formula that automatically expands when new rows appear.

You should use this method when all stakeholders work in Microsoft 365 or Excel 2021+, because dynamic arrays spill automatically. If your organisation still uses Excel 2016 or earlier, you can replicate the result with helper columns and INDEX/SMALL constructions, but they are less elegant and slower.

Logic overview:

1. Build a key that ranks numbers before text using --ISNUMBER(value) or IF(ISNUMBER(value),0,1).
2. Combine that key with the original list in SORTBY.
3. Optionally add a second level of sort—ascending numbers and then alphabetical text.

Recommended syntax:

CODE_BLOCK_0

• source_range = list that contains both text and numbers
• --ISNUMBER(...) converts TRUE/FALSE to 1/0, so numbers (1) or (0) can be placed first
• The 1 after each sort key means ascending order; use -1 for descending.

Alternative if you only need pure ascending without separating types:

CODE_BLOCK_1

The optional comparison function parameter (TRUE) forces a natural sort that aligns text-numbers such as “A2”, “A10”, “A11” correctly. Older versions lack that feature, so you would fall back on helper columns.

Parameters and Inputs

• Required input: [A2:A100] (or any contiguous vertical range). The data type can be General, Text, or Number—Excel will coerce as needed.
• Optional keys: additional arrays of equal height that dictate secondary, tertiary, and further sort levels.
• Sort order flag: 1 for ascending, -1 for descending.
• Comparison logic (only with SORT): match_mode accepts 1 (exact), -1 (approx), or 2 (wildcard). Natural text-number ordering happens when you set ignore_case in some beta builds; for compatibility we rely on the numeric key method described above.

Data preparation:

1. Remove trailing spaces with TRIM or CLEAN if the list is imported.
2. Ensure numbers stored as text (green triangle) are intentionally left that way; otherwise convert with VALUE or multiply by 1.
3. Remove blank rows if you want blanks at the bottom; otherwise leave them and treat blanks as text.

Edge cases:

• Error values (#N/A, #VALUE!) propagate through the sort; wrap source in IFERROR if you want to push them to the bottom.
• Mixed units like “5kg” behave as text; strip units before sorting if you need numeric order.
• Scientific notation strings (“1.23E+5”) are treated as text unless you coerce to number.

Step-by-Step Examples

Example 1: Basic Scenario

Assume [B3:B12] holds the unsorted list:

CODE_BLOCK_2

Goal: Ascending numbers first, then text A-Z.

Step 1 – Select an output cell, say D3.
Step 2 – Enter:

CODE_BLOCK_3

Explanation:

• ISNUMBER returns TRUE for 40,17,3,25,1 and FALSE for the text items.
• The double unary (--) converts TRUE to 1 and FALSE to 0. Sorting by that column ascending puts 0s (numbers) first.
• The second key B3:B12 sorts numbers and text internally (1,3,17,25,40 then Alpha,Beta,Central,North,South).
  Result spills automatically in D3:D12.

Common variations:

• Descend by appending -1 after a key.
• Push text first by flipping the first sort order to -1.

Troubleshooting: If numbers stored as text don’t appear with numbers, wrap the range in VALUE inside the first key.

Example 2: Real-World Application

Scenario: A procurement team maintains an inventory list in [A2:A50]. Items are either numeric part numbers (e.g., 10234) or special-order codes (e.g., “XSP-45”). They need a live, sorted dropdown for data validation so warehouse staff can pick items quickly.

Data setup:

• A2:A50 mixed list.
• Data validation dropdown should point to a named spill range.

Step-by-step:

1. Create a named range called InvList referring to [A2:A50].
2. In a helper sheet, cell B2, enter the dynamic sort formula:

CODE_BLOCK_4

3. Excel spills the sorted list downwards.
4. Define another name InvSorted as =B2#. The # symbol references the entire spill.
5. Back in the warehouse entry sheet, set data validation > List > =InvSorted.

Business impact: Whenever procurement appends new rows to [A2:A50], the spill range grows and the dropdown instantly reflects the updated ordering. That removes manual resorting and minimises data entry errors.

Integration: This named spill can feed pivot tables, XLOOKUP arrays, or be concatenated into comma-separated lists for ERP exports. Performance is excellent because dynamic arrays recalculate only when source cells change, not on every volatile event.

Example 3: Advanced Technique

Advanced need: You receive a monthly CSV of sales codes mixing pure numbers, text placeholders like “VOID”, and combined strings such as “A-102”. Management wants:

1. Pure numbers ascending
2. Alpha-numeric codes such as “A-102”, “B-7” sorted naturally (A before B, 7 before 102)
3. Text placeholders at the bottom

Solution uses three helper sort keys:

• Key 1: Type ranking (Number=1, AlphaNumeric=2, Text=3)
• Key 2: Letter component of AlphaNumeric (LEFT function)
• Key 3: Numeric component extracted with VALUE and MID

Formula in D2 for [C2:C500] source:

CODE_BLOCK_5

Explanation:

• The nested IF assigns a type ranking: pure numbers 1, alphanumeric 2, everything else 3.
• Secondary sort by first letter manages “A-” family before “B-”.
• Tertiary numeric extraction orders “A-7” before “A-102”.
  Edge case handling: IFERROR(...,9E+307) pushes non-numeric strings to the end within their letter group.

Performance: Even with 10,000 rows this formula recalculates in under a second on modern hardware because each function is vectorised. For legacy builds, split keys into explicit columns to avoid repetitive calculations.

Tips and Best Practices

1. Use named ranges for your source data so formulas remain readable and resilient to resize operations.
2. Anchor ranges that may grow by converting them to Excel Tables; references like Table1[Code] automatically expand.
3. Minimise volatile functions (e.g., TODAY, RAND) inside sort keys—volatility forces recalculation even when data has not changed.
4. Cache complex helper keys in hidden columns if performance lags; then point SORTBY to those columns instead of recalculating inside the function.
5. Combine with FILTER to produce interactive views such as “Top 20 numeric codes” by wrapping the sorted spill inside INDEX or TAKE.
6. Document logic with comments because multi-level SORTBY formulas can look intimidating; a quick note prevents future confusion.

Common Mistakes to Avoid

1. Assuming numbers stored as text will sort numerically. Text “100” sorts after “9”. Fix by coercing with VALUE or multiplying by 1 inside the numeric key.
2. Forgetting to lock absolute references when copying formulas manually. Use structured references or anchors to prevent accidental shifts.
3. Overlooking blanks and error cells. Blanks sort before text. Wrap source with FILTER(range, range<>"") to ignore blanks, or with IFERROR to push errors down.
4. Using SORT instead of SORTBY for mixed types and expecting numbers first. SORT only respects one native order; you need a custom key for mixed logic.
5. Neglecting version compatibility. Sharing a file that relies on dynamic arrays with colleagues on Excel 2016 yields a #NAME? error. Provide a fallback helper-column version or instruct them to enable Microsoft 365.

Alternative Methods

When to pick:

• Modern environments: Stick with SORTBY.
• Mixed workplace: Provide a helper-column fallback in hidden columns.
• Older files that must remain macro-free: Use INDEX+SMALL.
• Highly customised pipelines: VBA or Power Query may be preferable for heavy transformations.

FAQ

When should I use this approach?

Use formula-based sorting whenever the list needs to stay live—imported data changing daily, interactive dashboards, or data validation dropdowns. If you only sort once and lock the sheet, manual Sort is fine.

Can this work across multiple sheets?

Yes. Reference the source range with the sheet name, e.g., =SORTBY('Raw Data'!A2:A500, --ISNUMBER('Raw Data'!A2:A500),1, 'Raw Data'!A2:A500,1). The spill will appear on the sheet where you enter the formula; updates reflect immediately.

What are the limitations?

Dynamic arrays require Excel 365 or 2021+. There is also a 1,048,576-row limit per column; sorting beyond that requires Power Query or a database. Array formulas cannot spill into merged cells or a range containing other data.

How do I handle errors?

Wrap your source in IFERROR(source,"") to convert errors to blank strings, then they will naturally fall below numbers. Alternatively sort by ISERROR as your first key to push errors last.

Does this work in older Excel versions?

Not the exact SORTBY formula. For Excel 2019 and earlier, construct helper columns: Column B key =IF(ISNUMBER(A2),0,1), Column C =A2, then sort these columns with a standard Sort or with legacy array formulas.

What about performance with large datasets?

SORTBY scales well up to tens of thousands of rows. For hundreds of thousands, consider:

• Move heavy calculations (LEFT, MID) into helper columns.
• Store source data in an Excel Table to limit the spill target size.
• Use Power Query to pre-sort and then bring a smaller subset into the worksheet.

Conclusion

Sorting text and numbers with a single formula turns spreadsheets into self-maintaining assets. With SORTBY and a cleverly designed type key you can keep numeric IDs, mixed codes, and descriptive labels in perfect order, even as new records stream in. This skill supports dependable lookups, accurate dashboards, and frustration-free data entry. As you continue mastering dynamic arrays, experiment with FILTER, TAKE, and LET to build even richer automated models. Practise on live datasets, document your formulas, and enjoy the time you reclaim from manual resorting—Excel will do the heavy lifting for you.