• Hi
    I'm having a heck of a time soring some simple numbers, can anyone help? This is what I'm try to do. The first set of numbers is how excel sorted these numbers. But I want them sorted as in the second set of numbers. Use the ascending button will get you the first set. I don't know how to get all the 1's together and then all the 2's next ans so on reguardless of the number of digits.
    Thank you in advance for any help anyone can give me.
    1 -------- 1
    2 -------- 11
    3 -------- 111
    11 ------- 2
    22 ------- 22
    33 ------- 3
    111 ------ 33


    :thanx:

  • Using working column:
    If the data is in column A
    B1:
    =--LEFT(A1,1)
    fildown to the last row of col. A


    then sort by column b as first criteria and a for second.


    Hope this does good for you.

  • Hi CVA.


    Excel sees your list as numbers and sorts them accordingly. Your sort will only happen if Excel sees the list as text. You might try copying the list with the formula: =TEXT(A1,000) and then reformat the new list as text before sorting.



    Rich D

    If I've been helpful, let me know. If I haven't, let me know that too. 

  • Thank you guys for such a fast return to the post. I have tried both methods and they both seem to work for what I want it to do. I think it's best when entering numbers like this is to start by entering them as text. Then I wouldn't have to do this. But anyway thank you very much, you have solved my problem.
    :rock:

  • Sorry guys, I have a P.S. to this post. Seems that either way I do it, it works just great. I can now sort the numbers as text just the way I wanted. I know I should have started with entering the data as text. Now that I use one of these methods I now bump into another problem. I can't delete the original set of numbers now. If I do I get the error stating I have removed the original numbers. The column has the function in it and not the text. So now can I convert the =text(a1,0) function to the number now in the cell but as a text number and not a digit. I hope you can understand what I mean. Once again thank you guys.

  • Hi Jindon


    what I have is in colunm A I have the original numbers. When I sort these I get numbers going from the sallest to the largest number. Like the first colunm in my first post. When I go to colunm B and add the =text(a1,0) to B1 cell, I get the number to appear there. I then drag that function down to cover all the number in colunm A. It fills in all the text numbers for me. I can now sort this colunm just fine, and it sorts just like the second colunm above. If I now want to add to this list I have to add the numbers in colunm A and change the function in colunm B to match. What I would like to do before I go adding more numbers to my list is convert what I have to text so I can delete colunm A. But like I said when I do that I get an error telling me I am removeing the refferance numbers. So now I want to convert colum B the straight text. So when I click on a cell, at the top it will say it's a number and not a function. You say to use =text(a1,"@") , you'll have to forgive me, I'm not sure what you mean or even how to do what you are saying. If i put =text(a1,"@") in a cell is that not a function. If I do this and then click on the cell to highlight it, in the top window it shows =text(a1,"@") and not a number in text format.
    Thanks again

  • Hi,


    First of all, at this end, it doesn't sort like you desire even I change the format from number to text. The only way I can get sorted that way is what I mentioned above and it doesn't matter if the values in column A are formatted as number or text.


    Anyway, if you want convert numbers in column A to text in column B is as follow


    1) Copy columnA and select columnB then pastespecial and check Values then OK
    2) Right click and select cell format then change from General to Text


    Alternatively, the given formula in cell B1
    =TEXT(A1,"@")
    changes value to Text from numbers
    If you put =ISTEXT(B1) returns TRUE means Text already.


    I hope you can get what you wish,


    Jindon

  • Thank you Jindon. This works for me. I best leave the colunm a there and now start entering in colunm b. These numbers are now in text format and sort just the way I want them. I have to hide the original colunm a. If I delete it, I get the REF error. So everything seems to work the way I want it. Thanks a gain for the prompt replies.


    cva675

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!