You can sort text-formatted or multi-level (outline) numbering directly in the table or in a separate range in Google Sheets. For the first option, you will need a helper column. This solution may be new to you, as it’s not commonly encountered.

Multi-level numbering, also known as outline numbering, is commonly used in spreadsheets to organize data hierarchically. We typically use formats like 1, 1.1, 1.2, 2, 2.1, …, 10, 10.1, and so on, to represent sections and subsections. A regular sort will place “10” and “10.1” above “2” because these are treated as text values.

How do you sort these text-formatted multi-level numbers in numerical order in Google Sheets?

Sort Text-Formatted or Multi-Level Numbers with a Formula in Google Sheets

We have sample data in A1:B, with Item No. (outline numbers) in column A and Descriptions in column B.

You can use the following formula to sort multi-level numbers in Google Sheets for the range A2:B:

=SORT(
   FILTER(A2:B, A2:A<>""), 
   LET(
      separate, SPLIT(TOCOL(A2:A, 1), "."), 
      seqR, SEQUENCE(ROWS(separate)), 
      seqC, SEQUENCE(COLUMNS(separate), 1, 2), 
      qstring, "Col"&TEXTJOIN(", Col", TRUE, seqC), 
      qry, QUERY(HSTACK(seqR, separate), "SELECT Col1 ORDER BY "&qstring), 
      XMATCH(seqR, qry)
   ), 1
)

If you only want to sort the multi-level numbers in A2:A, replace A2:B with A2:A. If you have additional columns, replace A2:B accordingly.

How Does This Formula Work?

The formula splits the multi-level numbers at the “.” into multiple columns.

The QUERY function is used to dynamically sort each column in the split result in ascending order. The sort range in the QUERY includes the sequence number column as the first column, which is not included in sorting. The QUERY returns this column.

We then match the sequence numbers in the qry result for the correct relative position. Sorting the multi-level numbers using this column will give you the desired output.

This approach ensures precise sorting of multi-level numbers.

Breakdown of the Formula Components:

• separate:
  SPLIT(TOCOL(A2:A, 1), ".") – This splits the outline numbers based on the “.” delimiter into multiple columns. The TOCOL function ensures that empty rows are not included.
• seqR:
  SEQUENCE(ROWS(separate)) – This generates a sequence of numbers corresponding to the outline numbers.
• seqC:
  SEQUENCE(COLUMNS(separate), 1, 2) – This generates sequence numbers corresponding to columns after the split, starting from 2. For example, if there are 5 levels, the numbering will range from 2 to 6.
• qstring:
  "Col"&TEXTJOIN(", Col", TRUE, seqC) – Adds the text “Col,” to seqC to specify the sort order in QUERY, e.g., “Col2, Col3, Col4, …”
• qry:
  QUERY(HSTACK(seqR, separate), "SELECT Col1 ORDER BY "&qstring) – This combines the sequence numbers and split results. The formula sorts the split results by each column in ascending order and returns the sequence numbers in the first column.
• Formula Expression:
  XMATCH(seqR, qry) – Matches the row sequence of outline numbers in the query result.

Finally, we use the SORT function to sort the multi-level numbers by this XMATCH result as follows:

=SORT(FILTER(A2:B, A2:A<>""), ..., 1)

That’s how we precisely sort text-formatted or multi-level numbers in Google Sheets.

Sort Text-Formatted or Multi-Level Numbers Using the Sort Menu in Google Sheets

The formula-based approach sorts your existing data into a new range. If you prefer to sort the data in its original location, you can use the sort menu.

Consider the sample data in A1:B.

Insert the following formula in another column (for example, in cell C1):

=ArrayFormula(
   VSTACK(
      "HELPER", 
      LET(
         separate, SPLIT(TOCOL(A2:A, 1), "."), 
         seqR, SEQUENCE(ROWS(separate)), 
         seqC, SEQUENCE(COLUMNS(separate), 1, 2), 
         qstring, "Col"&TEXTJOIN(", Col", TRUE, seqC), 
         qry, QUERY(HSTACK(seqR, separate), "SELECT Col1 ORDER BY "&qstring), 
         XMATCH(seqR, qry)
      )
   )
)

This acts as the helper column.

Next, follow these steps to sort the data:

1. Select A1:C.
2. Click Data > Sort range > Advanced range sorting options.
3. In the window that appears, select “Data has header row.”
4. Choose “HELPER” under “Sort by.”
5. Click Sort.

This will sort the data by the text-formatted multi-level numbers in numerical order.

Resources

• Hierarchical Number Sorting in Excel with Modern Functions
• How to Properly Sort Alphanumeric Values in Google Sheets
• Sort by Custom Order in Google Sheets [How to Guide]
• Formula for Sorting by Month Name in Google Sheets
• Fix Array Formula Messing Up When Sorting in Google Sheets
• Filter or Sort by Font or Cell Color in Google Sheets – Built-in Feature
• Sort Items by Number of Occurrences in Google Sheets
• Sort by Day of Week in Google Sheets
• How to Custom Sort By Partial Match in Google Sheets
• Sorting Data Separated by Line Breaks within Google Sheets Cells