Is there any way to sort a column numerically?

AdrianV

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

Paul Newcome

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

@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

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

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

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?