Forum Discussion

RebeccaCalcutt's avatar
RebeccaCalcutt
Copper Contributor
Aug 25, 2022

Sorting

Hi, 

I have a column with values S-1 through S-5000. I want to sort the spreadsheet in numerical order of this column. How can I do this?

  • RebeccaCalcutt 

    Option #1: instead of S-1, S-2 etc., enter the numbers 1, 2 etc. in the column, and apply the custom number format "S-"0 to the cells. Since the values are now real numbers, you can sort them in numerical order.

     

    Option #2: use S-0001, S-0002, etc. These values, although alphanumeric, will sort the way you want.

     

    Option #3: let's say your values S-1, S-2 etc. are in A2 and down. Insert an empty column in column B.

    Enter the following formula in B2, then fill down: =--MID(A2,3,4)

    Sort on column B. You can hide column B if you wish.

Resources