Sorting a mixed text/number Column by number on a Mac

paulcechovicgmailcom 

It does not sound stupid; everyone starts learning Excel at the novice level.

And let me be clear that this is not Excel's Sort feature, which sorts a range of data in place.  A formula that includes a SORT or SORTBY function makes a copy of a range, with rows sorted however is specified in its arguments.

Fortunately, you need only one formula per sorted range, because the formula spills values into adjacent columns and rows.  You can see that it spills from a visual indicator – at least on the Windows version, hopefully the Mac version also – because when you select the cell with the formula, Excel draws a (temporary) thin blue boundary around the entire range into which it placed values (and a grayed-out copy of the formula, if you look in the Formula Bar when one of those other cells in the range is selected).

Here's the formula again, for reference:

=LET( IDs, $A2:$A7,  data_to_sort, $A2:$B7,
    numbers, LET( digits_only, LAMBDA(string, LET(
            explode, TOCOL(MID(string, SEQUENCE(LEN(string)), 1) * 1, 2),
            IFERROR(CONCAT(explode) * 1, "")
            ) ), BYROW(IDs, digits_only) ),
    alpha, MID(vector, LEN(numbers)+1, 25),
    SORTBY(data_to_sort, numbers, 1, alpha, 1)
)

So:

1. Take note of the number of columns and rows for the data you want to sort.
2. Decide where you want to place a (sorted) copy of the data range.  It can be on a different worksheet.  Be sure there is enough available room (columns and rows) for the copy.¹
3. Copy the formula.
4. Select the cell that is at the top left corner of the range from step 1.
5. Paste the formula into the Formula Bar.  (If you paste it into the cell, you'll probably break it up into multiple rows; not the desired result.)
6. (Optional) Increase the number of lines shown in the Formula Bar by dragging its lower boundary down.  (If it won't budge, try clicking inside the formula, press the down arrow key once or more, and try dragging the boundary again.)
7. Edit the first line of the formula: Change the $A2:$A7 to match the single-column range that has your identifiers. Change the $A2:$B7 to match the range that has all the data to be sorted.²
   If the formula is on a different worksheet than the data, prefix those two range addresses with the name of the other worksheet and an exclamation mark. If the other worksheet's name includes a space or a special character, surround it with apostrophes; e.g., 'Vendor Data'!$A2:$A7

¹ If any cells in the range where you try to put the copy are not empty, the formula will generate a #SPILL! error. That's OK, because Excel has protected you from overwriting some existing formulas/values. Once you clear those cells, the formula will do its work.
² In the example in my workbook, the Identifiers were in the leftmost column of the data range. The formula works even if the Identifiers are in some other column.

If you want, it may be possible to overwrite the original range with the sorted range. But do not attempt this if the original range contains any formulas. First save your work, in case something goes wrong. You would select all of the sorted range, press Ctrl+C (or Cmd+C, if on a Mac?), right click on the top left cell of the original range, and click the Paste Values icon on the popup menu (or Paste Special… and select the Values option).