How to sort mixed numbers and text, hierarchy numbers in Excel

The tutorial explains common problems with ordering alphanumeric strings in Excel and provides working solutions to sort numbers as text and text as numbers.

Sorting text and numbers separately in Excel is as easy as ABC or 123 :) But re-arranging product IDs, SKU's and other values that have both letters and digits in them may cause problems. Excel's alphabetical sort cannot distinguish a numeric part of a string to handle it separately. If you want to have more control over how alphanumeric stings are sorted, look through the examples below.

Sort numbers as text in Excel

When sorting a column of numbers from smallest to largest in Excel, instead of having this:

1, 2, 3, 11, 12, 13, 110, 120, 130

you may sometimes get something like this:

1, 11, 110, 12, 120, 13, 130, 2, 3

That happens when numbers are actually text and they sort as text - like words that are arranged based on their letters, "text-numbers" are sorted based on their digits instead of their values.

If the target column contains numbers formatted as text, all it takes for the numbers to sort normally is to convert text to number. The result is in column C in the screenshot below. Sort numbers in Excel.

On the contrary, if you wish to sort numbers as text, convert numbers to text first, and then click Home tab > Editing group > Sort & Filter > Sort A - Z. Sort numbers as text in Excel

The result will be like in column A in the previous screenshot.

How to sort mixed numbers and text in Excel

With the built-in Excel Sort feature, alphanumeric strings (combined text and numbers) are always sorted as text, i.e. letter-by-letter, digit-by-digit. To sort a column of numbers containing a letter prefix or suffix as numbers, carry out the steps below. And here's the result we are trying to achieve. Sort mixed numbers and text in Excel.

To prevent Excel from handling numbers in alphanumeric strings as text, we're going to extract those numbers in a helper column, and sort by that column. This will let you keep the original data unchanged but rearrange it the way you like.

  1. In a column next to the original values, enter a formula that extracts number from string.

    In this example, we can simply extract all the characters after a hyphen ("-"). In Excel 365, this can be done using the TEXTAFTER function. As with any other Text function, its output is always text, regardless of whether you are retrieving letters or digits, so we multiply the result by 1 to convert a text string to a number.

    =TEXTAFTER(A2, "-")*1

    In older Excel versions, the same result can be achieved using a combination of three different functions that extract text after a specific character. Additionally, the *1 operation is performed to convert the text output to number:

    =RIGHT(A2, LEN(A2) - SEARCH("-", A2)) *1 Extract a number from an alphanumeric string.

    Tip. To quickly extract numbers from any position in a cell, you can use the Extract tool included with our Ultimate Suite for Excel.

    Get a number from an alphanumeric string using the Extract tool.
  2. Sort the original data by the extracted numbers. With the numbers selected, go to the Home tab > Sort & Filter > Sort Smallest to Largest. In the dialog box that pops up, choose the Expand the selection option and click Sort. For more details, see How to sort and keep rows together. Sort strings by numbers.

    Now the strings containing letters and digits are sorted as numbers, and you can remove or hide the helper column if necessary. Text strings are sorted as numbers.

Sort strings by text and numbers at a time

In case your source strings have a few different elements, you can extract each element in a separate column, and then sort by multiple columns. As a result, the strings will be arranged like in column D in the screenshot below: Sorting strings by text and numbers

As all the strings in our dataset have the same pattern, the easiest way to split text and numbers into different columns is by using regular expressions. This can be done with the help of the custom function named RegExpExtract. First, you add its code to your Excel as explained in this tutorial. After that, make use of the following formulas.

To extract the first occurrence of text from cell A2, the formula in B2 is:

=RegExpExtract(A2, "[^\d]+", 1)

To extract the first number, the formula in C2 is:

=RegExpExtract(A2, "\d+", 1)*1

To extract the second number, the formula in D2 is:

=RegExpExtract(A2, "\d+", 2)*1

In the 2nd and 3rd formulas, we multiply the RegExpExtract output by 1 to turn an extracted substring into a number. Extract numbers and text into different columns.

The users of our Ultimate Suite can leverage the Regex Tools to achieve the same result. They will just need to perform one more quick operation to convert the extracted numeric strings (columns C and D) to numbers: Using the RegEx Tool to extract numbers and text.

Once the text and numbers are split, sort your dataset by multiple columns:

  1. Select all the columns (A2:D16 in our case).
  2. On the Data tab, in the Sort & Filter group, click the Sort button.
  3. Add as many sort levels as you need and choose the desired sort order.
  4. When done, click OK.

In our case, the selected range is first sorted by Text from A to Z, then by Number 1, and then by Number 2 from smallest to largest: Sort mixed text and numbers in Excel.

The result is exactly what we were looking for: Alphanumeric strings are sorted by text and number.

Tip. To re-arrange the strings differently, change the order of levels in the Sort dialog box.

How to sort multilevel / hierarchy numbers in Excel

In Microsoft Excel, multilevel numbers such as 1.1, 1.1.1, 1.1.2 are strings, and they sort as text, not numbers: Sorting hierarchy numbers in Excel

To sort multilevel strings as numbers, you can use a custom user-defined function that has the following syntax:

HierarchyNumber(range, del, max_level)

Where:

  • Range is the range to sort.
  • Del is the delimiter used for separating the levels, typically a point (.).
  • Max_level is the maximum number of levels in the hierarchy numbers.

And here is the function's code:

Custom function to sort hierarchy numbers
Function HierarchyNumber(range As range, del As String, max_level As Integer) As Double Dim part As Variant part = Split(range.Value, del) For Index = 0 To UBound(part) HierarchyNumber = HierarchyNumber + part(Index) * (10 ^ ((max_level - Index) * 2)) Next Index End Function

What the function does is generate numbers that allow sorting multilevel numeric strings. More precisely, it splits a hierarchy string into individual numbers (parts), processes each part based on the below algorithm, and adds up the results:

part(Index) * (10 ^ ((level - Index) * 2))

For example, for 1.1, the function produces this result:

1*(10^((2-0)*2)) + 1*(10^((2-1)*2)) = 10100

For 1.2, it's:

1*(10^((2-0)*2)) + 2*(10^((2-1)*2)) = 10200

How to sort hierarchy numbers using the custom function:

  1. Insert the code of the HierarchyNumber function into a standard code module of your workbook and save it as a macro-enabled workbook (.xlsm). The detailed instructions are here.
  2. In a blank cell next to the first hierarchy string, enter a HierarchyNumber formula and press the Enter key.
  3. Drag the formula down across as many rows as needed.
  4. Sort your dataset based on the column of formulas.

Suppose you have multilevel numeric strings with a maximum of 3 levels in column A. The formula for B2 is:

=HierarchyNumber(A2, ".", 3)

After copying the formula down, you'll get this result: Custom function for sorting multi-level numbers

After that, you sort the whole dataset (A2:B19) by the formula column from smallest to largest: Sort the multilevel strings by the formula column.

And get the multi-level numeric strings sorted as numbers: The multilevel numeric strings are sorted as numbers.

Sort multilevel numbers prefixed with text

This example discusses a slightly different case where multilevel numbers are prefixed with some text, say "Level 1.1.1". At first sight, the task sounds more complicated, but in fact the solution is simpler :)

First, you extract multilevel numeric substrings into a separate column. For this, you can use the Extract tool included with our Ultimate Suite: Use the Extract tool to pull multilevel numbers.

Or you can write a formula to pull all characters after a space:

=RIGHT(A2, LEN(A2) - SEARCH(" ", A2)) Extract multilevel numeric substrings into a separate column.

And then, do custom sorting. In the Excel Sort dialog box, add two levels - first by the extracted hierarchy number, and then by the original string - and click OK: Sort the dataset by the extracted hierarchy number and the original string.

The following warning will pop up, where you choose to "Sort numbers and numbers stored as text separately". Sort numbers and numbers stored as text separately.

As a result, the strings containing multi-level numbers are sorted by numbers: The strings containing multi-level numbers are sorted by numbers.

That's how to sort numeric strings and multi-level numbers in Excel. Thank you for reading and see you on our blog next week!

Available downloads

Sort mixed numbers and text in Excel - examples (.xlsm file)
Ultimate Suite fully-functional trial version (.exe file)

4 comments

  1. I find it really confusing and annoying that excel seems to sort numbers before text even when I set them all to be formatted as text. Example: if I have the set {14, 12A, 11-1, 16, 15b} and sort them A->Z they will be sorted as 14, 16, 11-1, 12A, 15b
    In my opinion, it would make much more sense if excel treated all content as text and sorted them to 11-1, 12A, 14, 15b, 16
    Meaning that what can be parsed as a number is always sorted separately from mixed text content regardless if I format everything as text.
    Is there any way to get the behaviour I expected?

  2. Please make a blog on the use of value type in conditional formatting, basically there are four types of condition formatting.... They are number formula percent and percentile.

    I am confused in percent and percentile type...... I can use them in my data but I don't know how they work and on what basis they get calculated and applied on my data.

    So please make a blog on this two types.

    • Hi Subroto,

      A percentage calculates numbers in relation to 100. A percentile is a score below which a given percentage of scores falls. For example, the 60th percentile is a number that is greater than 60% of the numbers in a given set.

      In relation to Excel conditional formatting, e.g. a default 3-color scale uses the 50th percentile for the midpoint. When creating a custom color scale you can set any value you want (percent or percentile) for min, max and midpoint.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)