Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Soft number with alphabet

  1. #1
    Guest

    Default

    Hi Excellers,

    I copy the following number from WORD to EXCEL. I try to soft its with ascending order. But its seem to be won't work for those number with alphabet. How can I ratify its. Thank you.

    For example:
    2001
    2002
    2004
    2005
    2007
    2008
    2010
    2003-L
    2006-O
    2009-HK

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    SRC
    Posts
    165
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Seems OK to me remember 2003 comes before 2003-A and that before 2003-C
    hope this helps

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    First, sort your list (this will sort 2000-a,2000-b, etc properly.) add a temporary column with the formula
    =IF(ISNUMBER(A1),A1,LEFT(A1,FIND("-",A1)-1)+0)
    and copy it down.

    Sort both columns by this new column. Then you can delete the new one if you like.

    Good luck.


    On 2002-02-22 05:35, Anonymous wrote:
    Hi Excellers,

    I copy the following number from WORD to EXCEL. I try to soft its with ascending order. But its seem to be won't work for those number with alphabet. How can I ratify its. Thank you.

    For example:
    2001
    2002
    2004
    2005
    2007
    2008
    2010
    2003-L
    2006-O
    2009-HK

  4. #4
    Board Regular George J's Avatar
    Join Date
    Feb 2002
    Location
    Edinburgh, Bonnie Scotland
    Posts
    957
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Unless you mean you want the screen to show:
    2001
    2002
    2003-L
    2004
    2005
    2006-O
    2007
    2008
    2009-HK
    2010

    If this is the case, put the following formula in the next column and copy it down for the length of your entries. Then select the 2 columns and sort by the one with the formula.

    =LEFT(A1,4)
    George J

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    Malaysia
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi JohnG. IML, George J anfd others:

    Thank you for your advice.

    I try your formula its seem to be won't work. The number still show in the following order:
    2001
    2002
    2004
    2005
    2007
    2008
    2010
    2003-L
    2006-O
    2009-HK

    I want the number show in the following order:

    2001
    2002
    2003-L
    2004
    2005
    2006-O
    2007
    2008
    2009-HK
    2010

    I doubt whether is FORMAT problem. (Since my data is directly copy from MS WORD).

    Mark

  6. #6
    New Member
    Join Date
    Feb 2002
    Location
    London / NZ
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Put your list in column A and put the following formula in the correspondin cells column B:

    =LEFT(A1,4)&MID(A1,5,3)

    Then sort by Column B - you will get the result you are after.

  7. #7
    New Member
    Join Date
    Feb 2002
    Location
    Malaysia
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the suggestion.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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