In Excel online, how do I sort ascending with numbers & numbers with text at the end of the number?

Enter this formula in cell A2 of sheet2

=SORTBY(Sheet1!B3:S3475,REGEXEXTRACT(Sheet1!A3:A3475,"\d+",1))

Hope this helps.

[edited; option was of no interest]

Hi,

In cell B2, enter this formula

=SORTBY(A2:A5,REGEXEXTRACT(A2:A5,"\d+",1))

Hope this helps.

Hi @KAREN DOMINY,

I hope you have a good day.

If your column contains values like 50, 50x, 51, 51x, a normal A–Z sort treats them as text and won’t reliably keep each number, and suffix grouped the way you want. A workaround in Excel for the web is to add a helper column that extracts the numeric part, then perform a multi-level (Custom) sort.

Method A: Helper column + Custom sort (Excel for the web)

1. Add a helper column for the number part (e.g., in B2) and fill down. Example formula (works even when there’s no “x”):

      =VALUE(LEFT(A2, FIND("x", A2&"x")-1))
   

   (This extracts 50 from “50” and also 50 from “50x”.)
2. Add a helper column for the suffix flag (e.g., in C2) and fill down. Example formula:

      =IF(RIGHT(A2,1)="x",1,0)
   

   (No suffix = 0, has “x” = 1.)
3. Select your data range (including helper columns), then go to Data > Custom Sort and sort:
   • Sort by: the number-part column (B) > Sort Ascending
   • Then by: the suffix-flag column (C) > Sort Ascending

Tip: If sorting ever looks “off,” check whether some values are stored as numbers and others as text, mixed types can change sort behavior.

This produces the natural order you described (like 50, 50x, 51, 51x).

Method B: Using Helper Column in Excel application

Step 1: Create the helper column

• Insert a new column next to your data (let’s assume your data is in Column A, starting at A2).
• In cell B2, enter the following formula:

=--TEXTJOIN("", TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1) * 1, ""))

• Drag this formula down.

Step 2: Perform a multi-level sort

• Select your entire data range (including the new helper column).
• Go to the Data tab and select Sort.
• In the Sort dialog, set up two levels:
  • First Level: Sort by Column B (Helper) | Smallest to Largest.
  • Second Level: Click Add Level, then sort by Column A (Original) | A to Z.
  • 
• Click OK.

I hope this helps! If the reply resolved your question, please consider marking it as the accepted answer so others with the same sorting scenario can find it more easily.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.