+ Reply to Thread
Results 1 to 9 of 9

Sorting problem

  1. #1
    Registered User
    Join Date
    10-01-2007
    Posts
    8

    Sorting problem

    How can I sort the data on the left to become on the right? Do I have to manually fill in "0" for A1-A9 to become A01-A09?

    A1 A1
    A10 A2
    A11 A3
    A2 A4
    A3 A5
    A4 A6
    A5 A7
    A6 A8
    A7 A9
    A8 A10
    A9 A11

  2. #2
    Registered User
    Join Date
    09-23-2007
    Location
    India
    Posts
    11

    Sorting Problem

    Hi,

    Yes, you are right. you need to change "A1 - A9" to "A01 - A09". Then only we can sort these as required.
    Thanks

    Vinay Shrivastava

    vinayshrivastava11@yahoo.co.in

  3. #3
    Registered User
    Join Date
    10-01-2007
    Posts
    8
    Thank you, Vinay. Is there a way to insert "0" using formula?

    Mark

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,637
    If the data is as simple as you show, then you can custom format the cells as "A"0 and not worry about the leading zeros; Excel will only see the numbers for sorting purposes.

  5. #5
    Registered User
    Join Date
    10-01-2007
    Posts
    8
    Hi shg,

    The example is an abbreviated version. The actual data contains from A0 through A123. There are a lot of "0" and "00" I need to add.

    Could you clarify your suggestion?

    Thanks.
    Mark

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,637
    Sure. Select the cells of interest, then do:

    Format > Cells > Number > Custom, and enter "A"0 under Type.

    Then in some cell in an out-of-the-way column (starting in, say, K1),

    =VALUE(MID(A1,2,100) replacing A1 in the formula with the cell reference where the "A1", "A2", ... appear, and copy this formula down to capture all of the "A" numbers.

    Then copy the cells in the new column, and do Edit > PasteSpecial > Values over the top of the "A1", "A2", ... values. Then delete or clear the extra column.

    Now those values are just numbers with an "A" in the front, and they will sort in normal fashion.

    Capiche?
    Last edited by shg; 10-02-2007 at 05:56 PM.

  7. #7
    Registered User
    Join Date
    10-01-2007
    Posts
    8
    Hi shg,

    Got it! Only one minor deviation. When formatting the cells, I need to key in "A"#, instead of "A"1.

    Thank you very much.
    Mark

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,637
    You're welcome.
    ... instead of "A"1
    My suggestion was "A"0

  9. #9
    Registered User
    Join Date
    09-23-2007
    Location
    India
    Posts
    11

    Sorting Problem

    Hi 10036760,

    Please try this also. Suppose you have data in A coumn. Enter number of maximum digits in E1 and enter the below formula in B column to get the result as shown below:

    =LEFT(A1,1)&REPT("0",E$1-LEN(A1))&RIGHT(A1,LEN(A1)-1)

    A1 A0001 Maximum Digits 5
    A2 A0002
    A3 A0003
    A4 A0004
    A5 A0005
    A6 A0006
    A7 A0007
    A8 A0008
    A9 A0009
    A10 A0010

    This will insert all the required 0.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1