Sorting Rows Ascending Numerically

RainbowMoonbeams

I right click on the column and select "Sort Rows" and select "Sort Items By" the name of the column. They arrange numerically but it's broken up into numbers 007-099 showing up below 103-180. I want the 007-099 numbers to show up above 103-180 (as it should).

My thought is that the numbers are "text"? But I can't find where to assign the type of content in the cell.

Any help would be appreciated!

Paul Newcome

You are correct in that they are being read as text. You will need to create a hidden helper column with the following in it:

 

=VALUE([Original Number Column]@row)

.

This will convert it to an actual number (which will remove the leading zeroes), and you would then sort by that helper column.

RainbowMoonbeams

Paul, thank you for your reply.

I need to keep the leading 0's in there because they are part of the information that is stored in that column. Do you think they will still affect the way they after sorting after creating the hidden column?

In your formula, do I replace "Original Number Column" with the column name?

Paul Newcome

The leading zeroes will remain in the original column. They just won't be there in the Helper column.

 

You would sort based on the hidden helper column, so the data will remain intact in the original column.

 

"Original Number Column" would be replaced with the column name housing the numbers that contain the leading zeroes.

RainbowMoonbeams

Oh, I see! That is an interesting concept.

Thank you! I will try that.

Paul Newcome

Happy to help! 

 

Whenever I have a column like this specifically for sorting, I will move it to the far left of the sheet and then hide it. You won't see it when looking at the sheet itself, but when you go to sort, it will be the very first column on the list. I also name it something like "Sorting Column" or something along those lines so it is obvious that's which column I want to sort by.