Is there any way to sort a column numerically?

Options

This blows my mind a bit but if you have a column formatted as text/number, and you go to sort it, it will only read the first digit when sorting. So numbers from 10-19 would show up before 2...

This should be a very basic feature for any sort of spreadsheet

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    It will sort numerically if the cells contain numerical data. It sounds to ne that you have text strings that only look like numbers. Exactly how is your column being populated?

  • AdrianV
    AdrianV ✭✭
    Options

    @Paul Newcome The option for the column "type" is "text/numbers" so if the "numbers" part will still be read as a text string that that is still misleading. The data is being entered manually and it only contains numbers.

  • AdrianV
    AdrianV ✭✭
    Options

    Also if you have a set of addresses, you would expect the following order. This is an alphanumerical string, but should still be sorted numerically first:

    1 Sample Rd

    2 Sample Rd

    111 Sample Rd

    not the following (what smartsheet does):

    1 Sample Rd

    111 Sample Rd

    2 Sample Rd

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Text/number is for both text strings and numerical values. When you combine any kind of letter, space, etc. with a number, it is considered a text string and will sort as text strings. It isn't so much that numbers are read as text strings, it is more that you can have a text string that looks like numbers.


    Example: Wrapping the output of a formula in quotes will have it stored as a text string.

    =IF([Column Name]@row = "text", "1")

    The formula above will show 1 in the cell, but because it is wrapped in quotes it is stored as a text string.


    =IF([Column name]@row = "text", 1)

    The formula above will also show 1 in the cell, but will be stored as a numerical value.


    It works the same way with a few other things such as dates.

    ="01/01/2022"

    and

    =DATE(2022, 01, 01)

    will both LOOK the same in a cell, but only the second one will be stored as a date.


    If you remove all formatting from this text/number column, are all of the cells left justified, right justified, or a mixture of both? Are you able to provide a screenshot of these numbers in the sheet?

  • AdrianV
    AdrianV ✭✭
    Options

    I've since converted the column to a dropdown column which seems to circumvent the issue. I can't exactly change all the values without fudging some data.

    It seems as though the column has somehow been marked as "text" and any new data is now being treated as a text string.

    Is there any way to restore the format of a column? Or remove existing formatting?