Sort numbers stored as text

  • I have 3 columns one of which contains numbers stored as text which can contain values (00, 0 to 99). Now i need to sort this column in ascending order. After sorting i need 00 on top of the row and then the remaining numbers in ascending order. When i sort using below code, if I make "0" entry before "00", the result after sort is "0" before "00". if the entry is made vice versa, I get "00" before "0". My requirement is i require 00 to be at the top in all cases after sort.


    ------------------------------------------------------------------------

  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post


    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.


    How to use code tags


    Just highlight all of the code and press the <> in the post menu above button to add the code tags.


    Thanks.

  • When sorting "00" is treated the same as "0". Assuming you are using whole numbers only then a work-around is to first change the "0"s to "0.1"s, then sort, then change the "0.1"s back to "0"s.


    Try replacing the code you have with this

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Post by ajoy123rocks ().

    This post was deleted by the author themselves ().
  • Ok I will check on that later when I get home


    Can you attach a sample file

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • In KjBox's code, change this:


    Code
    1. .Range("F13:F24").Replace "0", "0.1"


    to this:


    Code
    1. .Range("F13:F24").Replace "0", "0.1", xlWhole

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • exactly

    I can't think of anything else than this pretty cheap solution:





  • Thanks Rory, I just got back and was about to post the same solution.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.