Sort Text-Formatted or Multi-Level Numbers in Google Sheets

Published on

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.

Formula-based sorting of text-formatted multi-level numbers in Google Sheets

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.

Helper column for sorting outline numbers using the sort menu in Google Sheets

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.

Result after sorting multi-level numbers using the sort menu in Google Sheets

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.