Sorting Rows Ascending Numerically

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
    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.

  • 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
    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.

  • Oh, I see! That is an interesting concept.

    Thank you! I will try that.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! yes


    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.