Copper Contributor


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?

1 Reply


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.