Sorting Alphanumeric data in Excel 2003

Discussion in 'Microsoft Excel Setup' started by Guest, Jun 15, 2007.

  1. Guest

    Guest Guest

    Trying to sort several part numbers - example
    23476
    1237X
    1237A
    355E2
    351E3
    74477
    111E6

    It will not sort correctly because of the numbers are treated seperately and
    the =TEXT(ref cell, "format") function treats the part #'s with "E2" or "E3"
    (basically E#) as scientific notation. Rekeying is not an option, as the
    actual sheet has several thousand of these types of mixed alpha numerics. It
    worked properly in Excel 2002 and Excel 2000, Excel 2003 has changed the
    whole thing!.
    --
    /billd
     
    Guest, Jun 15, 2007
    #1
    1. Advertisements

  2. Hi,

    What is the expected result after you sort the data?

    First, I set the cell format as Text and then copy the data into the cell.
    Thus, 111E6 will not be changed to 1.11E+08.

    Based on my testing, in Excel 2000, it will be sorted as the following:
    111E6
    1237A
    1237X
    23476
    351E3
    355E2
    74477

    In Excel 2003, when you sort the data and choose the option "sort numbers
    and numbers stored as text separately", it will be sorted as the following,
    same as in Office 2000:
    111E6
    1237A
    1237X
    23476
    351E3
    355E2
    74477

    In Excel 2003, when you sort the data and choose the option "sort anything
    that looks like a number, as a number", it will be sorted as the following:
    23476
    355E2
    74477
    351E3
    111E6
    1237A
    1237X

    Is the situation same on your computer? If not, please write down the
    detail steps which you did and cause what result. And let me know your
    expected result.

    Also, you can send the specific Excel file to me and let me know what you
    want to do in the Excel file. Thus, we can address the issue more
    efficiently. My Email address is .

    Following is the KB article about "Sorting alphanumeric text as numeric
    values":
    214282 Sorting alphanumeric text as numeric values
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;214282

    If anything is unclear or if you have any other concerns, please don't
    hesitate to contact me.

    Happy weekend!

    Regards,

    Emily Lin

    Microsoft Online Partner Support
    Get Secure! - www.microsoft.com/security
    ====================================================
    When responding to posts, please "Reply to Group" via your newsreader so
    that others may learn and benefit from your issue.
    ====================================================
    This posting is provided "AS IS" with no warranties, and confers no rights.


    --------------------
    | Thread-Topic: Sorting Alphanumeric data in Excel 2003
    | thread-index: Aceu++s170FUIm7EQzOlrR9ngTdOcQ==
    | X-WBNR-Posting-Host: 207.46.193.207
    | From: =?Utf-8?B?YmlsbGQ=?= <>
    | Subject: Sorting Alphanumeric data in Excel 2003
    | Date: Thu, 14 Jun 2007 20:19:00 -0700
    | Lines: 17
    | Message-ID: <>
    | MIME-Version: 1.0
    | Content-Type: text/plain;
    | charset="Utf-8"
    | Content-Transfer-Encoding: 7bit
    | X-Newsreader: Microsoft CDO for Windows 2000
    | Content-Class: urn:content-classes:message
    | Importance: normal
    | Priority: normal
    | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826
    | Newsgroups: microsoft.public.excel.setup
    | Path: TK2MSFTNGHUB02.phx.gbl
    | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.excel.setup:1141
    | NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
    | X-Tomcat-NG: microsoft.public.excel.setup
    |
    | Trying to sort several part numbers - example
    | 23476
    | 1237X
    | 1237A
    | 355E2
    | 351E3
    | 74477
    | 111E6
    |
    | It will not sort correctly because of the numbers are treated seperately
    and
    | the =TEXT(ref cell, "format") function treats the part #'s with "E2" or
    "E3"
    | (basically E#) as scientific notation. Rekeying is not an option, as the
    | actual sheet has several thousand of these types of mixed alpha numerics.
    It
    | worked properly in Excel 2002 and Excel 2000, Excel 2003 has changed the
    | whole thing!.
    | --
    | /billd
    |
     
    Emily Lin [MSFT], Jun 15, 2007
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    Thanks Emily, basically the trick is that you need to use an empty column,
    format it as text, copy the data into it, and then you can sort properly
    using Data | Sort... as you described. You cannot format the column already
    containing the data to a "text" column, and that was probably my issue...
    --
    /billd


    "Emily Lin [MSFT]" wrote:

    > Hi,
    >
    > What is the expected result after you sort the data?
    >
    > First, I set the cell format as Text and then copy the data into the cell.
    > Thus, 111E6 will not be changed to 1.11E+08.
    >
    > Based on my testing, in Excel 2000, it will be sorted as the following:
    > 111E6
    > 1237A
    > 1237X
    > 23476
    > 351E3
    > 355E2
    > 74477
    >
    > In Excel 2003, when you sort the data and choose the option "sort numbers
    > and numbers stored as text separately", it will be sorted as the following,
    > same as in Office 2000:
    > 111E6
    > 1237A
    > 1237X
    > 23476
    > 351E3
    > 355E2
    > 74477
    >
    > In Excel 2003, when you sort the data and choose the option "sort anything
    > that looks like a number, as a number", it will be sorted as the following:
    > 23476
    > 355E2
    > 74477
    > 351E3
    > 111E6
    > 1237A
    > 1237X
    >
    > Is the situation same on your computer? If not, please write down the
    > detail steps which you did and cause what result. And let me know your
    > expected result.
    >
    > Also, you can send the specific Excel file to me and let me know what you
    > want to do in the Excel file. Thus, we can address the issue more
    > efficiently. My Email address is .
    >
    > Following is the KB article about "Sorting alphanumeric text as numeric
    > values":
    > 214282 Sorting alphanumeric text as numeric values
    > http://support.microsoft.com/default.aspx?scid=kb;EN-US;214282
    >
    > If anything is unclear or if you have any other concerns, please don't
    > hesitate to contact me.
    >
    > Happy weekend!
    >
    > Regards,
    >
    > Emily Lin
    >
    > Microsoft Online Partner Support
    > Get Secure! - www.microsoft.com/security
    > ====================================================
    > When responding to posts, please "Reply to Group" via your newsreader so
    > that others may learn and benefit from your issue.
    > ====================================================
    > This posting is provided "AS IS" with no warranties, and confers no rights.
    >
    >
    > --------------------
    > | Thread-Topic: Sorting Alphanumeric data in Excel 2003
    > | thread-index: Aceu++s170FUIm7EQzOlrR9ngTdOcQ==
    > | X-WBNR-Posting-Host: 207.46.193.207
    > | From: =?Utf-8?B?YmlsbGQ=?= <>
    > | Subject: Sorting Alphanumeric data in Excel 2003
    > | Date: Thu, 14 Jun 2007 20:19:00 -0700
    > | Lines: 17
    > | Message-ID: <>
    > | MIME-Version: 1.0
    > | Content-Type: text/plain;
    > | charset="Utf-8"
    > | Content-Transfer-Encoding: 7bit
    > | X-Newsreader: Microsoft CDO for Windows 2000
    > | Content-Class: urn:content-classes:message
    > | Importance: normal
    > | Priority: normal
    > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826
    > | Newsgroups: microsoft.public.excel.setup
    > | Path: TK2MSFTNGHUB02.phx.gbl
    > | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.excel.setup:1141
    > | NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
    > | X-Tomcat-NG: microsoft.public.excel.setup
    > |
    > | Trying to sort several part numbers - example
    > | 23476
    > | 1237X
    > | 1237A
    > | 355E2
    > | 351E3
    > | 74477
    > | 111E6
    > |
    > | It will not sort correctly because of the numbers are treated seperately
    > and
    > | the =TEXT(ref cell, "format") function treats the part #'s with "E2" or
    > "E3"
    > | (basically E#) as scientific notation. Rekeying is not an option, as the
    > | actual sheet has several thousand of these types of mixed alpha numerics.
    > It
    > | worked properly in Excel 2002 and Excel 2000, Excel 2003 has changed the
    > | whole thing!.
    > | --
    > | /billd
    > |
    >
    >
     
    Guest, Jun 15, 2007
    #3
  4. Hi William,

    Thanks for your reply and the Excel attachment.

    First, I would like to clarify that:

    The situation (Set column J as Text > Column G is Number > copy column G to
    column J > it is also Number in Column J) is normal. It is because that the
    cell format is also copied when you copy/paste in Excel. In my first
    response, I said that I set cell format as Text and then copy the data from
    Notepad to Excel. So, the cell format is still Text.

    If you want to keep the column J as Text when pasting, please copy column G
    > then right click Column J > choose Paste Special > choose Value to only

    copy value into it. Thus, the cell format is still Text.

    Based on my testing on your Excel file, I can sort it properly. Following
    is my testing. If you still cannot sort it on your computer, please let me
    know the detail steps you did and what result you encountered.

    1. Select Column G and sort it. And choose option 2 "sort numbers and
    numbers stored as text separately". It is sorted as you want.

    2. Select Column G > right click it and choose "Format Cells" > choose
    Text, click OK. Sort it again and choose option 2 "sort numbers and numbers
    stored as text separately". It is sorted as you want.

    If anything is unclear or if you have any other concerns, please don't
    hesitate to contact me.

    Sincerely,

    Emily Lin,
    Microsoft Online Partner Support

    Get Secure! - www.microsoft.com/security

    ======================================================
    PLEASE NOTE: The partner managed newsgroups are provided to assist with
    break/fix issues and simple how to questions.

    We also love to hear your product feedback!
    Let us know what you think by posting
    from the web interface: Partner Feedback
    from your newsreader:
    microsoft.private.directaccess.partnerfeedback.
    We look forward to hearing from you!
    ======================================================
    When responding to posts, please "Reply to Group" via your newsreader so
    that others may learn and benefit from this issue.
    ======================================================
    This posting is provided "AS IS" with no warranties, and confers no rights.
    ======================================================

    --------------------
    | Thread-Topic: Sorting Alphanumeric data in Excel 2003
    | thread-index: AcevSZc0o7rVplEOTpqfMbf7Je8gBg==
    | X-WBNR-Posting-Host: 207.46.192.207
    | From: =?Utf-8?B?YmlsbGQ=?= <>
    | References: <>
    <>
    | Subject: RE: Sorting Alphanumeric data in Excel 2003
    | Date: Fri, 15 Jun 2007 05:35:00 -0700
    | Lines: 125
    | Message-ID: <>
    | MIME-Version: 1.0
    | Content-Type: text/plain;
    | charset="Utf-8"
    | Content-Transfer-Encoding: 7bit
    | X-Newsreader: Microsoft CDO for Windows 2000
    | Content-Class: urn:content-classes:message
    | Importance: normal
    | Priority: normal
    | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826
    | Newsgroups: microsoft.public.excel.setup
    | Path: TK2MSFTNGHUB02.phx.gbl
    | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.excel.setup:1145
    | NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
    | X-Tomcat-NG: microsoft.public.excel.setup
    |
    | Thanks Emily, basically the trick is that you need to use an empty
    column,
    | format it as text, copy the data into it, and then you can sort properly
    | using Data | Sort... as you described. You cannot format the column
    already
    | containing the data to a "text" column, and that was probably my issue...
    | --
    | /billd
    |
    |
    | "Emily Lin [MSFT]" wrote:
    |
    | > Hi,
    | >
    | > What is the expected result after you sort the data?
    | >
    | > First, I set the cell format as Text and then copy the data into the
    cell.
    | > Thus, 111E6 will not be changed to 1.11E+08.
    | >
    | > Based on my testing, in Excel 2000, it will be sorted as the following:
    | > 111E6
    | > 1237A
    | > 1237X
    | > 23476
    | > 351E3
    | > 355E2
    | > 74477
    | >
    | > In Excel 2003, when you sort the data and choose the option "sort
    numbers
    | > and numbers stored as text separately", it will be sorted as the
    following,
    | > same as in Office 2000:
    | > 111E6
    | > 1237A
    | > 1237X
    | > 23476
    | > 351E3
    | > 355E2
    | > 74477
    | >
    | > In Excel 2003, when you sort the data and choose the option "sort
    anything
    | > that looks like a number, as a number", it will be sorted as the
    following:
    | > 23476
    | > 355E2
    | > 74477
    | > 351E3
    | > 111E6
    | > 1237A
    | > 1237X
    | >
    | > Is the situation same on your computer? If not, please write down the
    | > detail steps which you did and cause what result. And let me know your
    | > expected result.
    | >
    | > Also, you can send the specific Excel file to me and let me know what
    you
    | > want to do in the Excel file. Thus, we can address the issue more
    | > efficiently. My Email address is .
    | >
    | > Following is the KB article about "Sorting alphanumeric text as numeric
    | > values":
    | > 214282 Sorting alphanumeric text as numeric values
    | > http://support.microsoft.com/default.aspx?scid=kb;EN-US;214282
    | >
    | > If anything is unclear or if you have any other concerns, please don't
    | > hesitate to contact me.
    | >
    | > Happy weekend!
    | >
    | > Regards,
    | >
    | > Emily Lin
    | >
    | > Microsoft Online Partner Support
    | > Get Secure! - www.microsoft.com/security
    | > ====================================================
    | > When responding to posts, please "Reply to Group" via your newsreader
    so
    | > that others may learn and benefit from your issue.
    | > ====================================================
    | > This posting is provided "AS IS" with no warranties, and confers no
    rights.
    | >
    | >
    | > --------------------
    | > | Thread-Topic: Sorting Alphanumeric data in Excel 2003
    | > | thread-index: Aceu++s170FUIm7EQzOlrR9ngTdOcQ==
    | > | X-WBNR-Posting-Host: 207.46.193.207
    | > | From: =?Utf-8?B?YmlsbGQ=?= <>
    | > | Subject: Sorting Alphanumeric data in Excel 2003
    | > | Date: Thu, 14 Jun 2007 20:19:00 -0700
    | > | Lines: 17
    | > | Message-ID: <>
    | > | MIME-Version: 1.0
    | > | Content-Type: text/plain;
    | > | charset="Utf-8"
    | > | Content-Transfer-Encoding: 7bit
    | > | X-Newsreader: Microsoft CDO for Windows 2000
    | > | Content-Class: urn:content-classes:message
    | > | Importance: normal
    | > | Priority: normal
    | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826
    | > | Newsgroups: microsoft.public.excel.setup
    | > | Path: TK2MSFTNGHUB02.phx.gbl
    | > | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.excel.setup:1141
    | > | NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
    | > | X-Tomcat-NG: microsoft.public.excel.setup
    | > |
    | > | Trying to sort several part numbers - example
    | > | 23476
    | > | 1237X
    | > | 1237A
    | > | 355E2
    | > | 351E3
    | > | 74477
    | > | 111E6
    | > |
    | > | It will not sort correctly because of the numbers are treated
    seperately
    | > and
    | > | the =TEXT(ref cell, "format") function treats the part #'s with "E2"
    or
    | > "E3"
    | > | (basically E#) as scientific notation. Rekeying is not an option, as
    the
    | > | actual sheet has several thousand of these types of mixed alpha
    numerics.
    | > It
    | > | worked properly in Excel 2002 and Excel 2000, Excel 2003 has changed
    the
    | > | whole thing!.
    | > | --
    | > | /billd
    | > |
    | >
    | >
    |
     
    Emily Lin [MSFT], Jun 18, 2007
    #4
  5. Hi William,

    Thanks for your reply.

    Based on my testing in Excel 2000/2002/2003, the sort results are same.
    They are as following:
    111410
    111430
    111571
    111581
    111611
    111631
    111951
    ...
    ...
    ...
    1117A1
    1117A2
    1117A3
    1117E1

    Do you mean that you can sort the data as you expected in Excel 2000/2002?

    At this point, please refer to the following KB article to see if you can
    sort the data as you expected in Excel 2003. I do appreciate your time and
    efforts on this issue.

    322067 How to correctly sort alphanumeric data in Excel
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;322067

    If anything is unclear or if you have any other concerns, please don't
    hesitate to contact me.

    Sincerely,

    Emily Lin,
    Microsoft Online Partner Support

    Get Secure! - www.microsoft.com/security

    ======================================================
    When responding to posts, please "Reply to Group" via your newsreader so
    that others may learn and benefit from this issue.
    ======================================================
    This posting is provided "AS IS" with no warranties, and confers no rights.
    ======================================================

    --------------------
    | X-Tomcat-ID: 53909737
    | References: <>
    <>
    <>
    | MIME-Version: 1.0
    | Content-Type: text/plain
    | Content-Transfer-Encoding: 7bit
    | From: (Emily Lin [MSFT])
    | Organization: Microsoft
    | Date: Mon, 18 Jun 2007 05:59:08 GMT
    | Subject: RE: Sorting Alphanumeric data in Excel 2003
    | X-Tomcat-NG: microsoft.public.excel.setup
    | Message-ID: <D$>
    | Newsgroups: microsoft.public.excel.setup
    | Lines: 190
    | Path: TK2MSFTNGHUB02.phx.gbl
    | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.excel.setup:1150
    | NNTP-Posting-Host: TOMCATIMPORT1 10.201.218.122
    |
    | Hi William,
    |
    | Thanks for your reply and the Excel attachment.
    |
    | First, I would like to clarify that:
    |
    | The situation (Set column J as Text > Column G is Number > copy column G
    to
    | column J > it is also Number in Column J) is normal. It is because that
    the
    | cell format is also copied when you copy/paste in Excel. In my first
    | response, I said that I set cell format as Text and then copy the data
    from
    | Notepad to Excel. So, the cell format is still Text.
    |
    | If you want to keep the column J as Text when pasting, please copy column
    G
    | > then right click Column J > choose Paste Special > choose Value to only
    | copy value into it. Thus, the cell format is still Text.
    |
    | Based on my testing on your Excel file, I can sort it properly. Following
    | is my testing. If you still cannot sort it on your computer, please let
    me
    | know the detail steps you did and what result you encountered.
    |
    | 1. Select Column G and sort it. And choose option 2 "sort numbers and
    | numbers stored as text separately". It is sorted as you want.
    |
    | 2. Select Column G > right click it and choose "Format Cells" > choose
    | Text, click OK. Sort it again and choose option 2 "sort numbers and
    numbers
    | stored as text separately". It is sorted as you want.
    |
    | If anything is unclear or if you have any other concerns, please don't
    | hesitate to contact me.
    |
    | Sincerely,
    |
    | Emily Lin,
    | Microsoft Online Partner Support
    |
    | Get Secure! - www.microsoft.com/security
    |
    | ======================================================
    | PLEASE NOTE: The partner managed newsgroups are provided to assist with
    | break/fix issues and simple how to questions.
    |
    | We also love to hear your product feedback!
    | Let us know what you think by posting
    | from the web interface: Partner Feedback
    | from your newsreader:
    | microsoft.private.directaccess.partnerfeedback.
    | We look forward to hearing from you!
    | ======================================================
    | When responding to posts, please "Reply to Group" via your newsreader so
    | that others may learn and benefit from this issue.
    | ======================================================
    | This posting is provided "AS IS" with no warranties, and confers no
    rights.
    | ======================================================
    |
    | --------------------
    | | Thread-Topic: Sorting Alphanumeric data in Excel 2003
    | | thread-index: AcevSZc0o7rVplEOTpqfMbf7Je8gBg==
    | | X-WBNR-Posting-Host: 207.46.192.207
    | | From: =?Utf-8?B?YmlsbGQ=?= <>
    | | References: <>
    | <>
    | | Subject: RE: Sorting Alphanumeric data in Excel 2003
    | | Date: Fri, 15 Jun 2007 05:35:00 -0700
    | | Lines: 125
    | | Message-ID: <>
    | | MIME-Version: 1.0
    | | Content-Type: text/plain;
    | | charset="Utf-8"
    | | Content-Transfer-Encoding: 7bit
    | | X-Newsreader: Microsoft CDO for Windows 2000
    | | Content-Class: urn:content-classes:message
    | | Importance: normal
    | | Priority: normal
    | | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826
    | | Newsgroups: microsoft.public.excel.setup
    | | Path: TK2MSFTNGHUB02.phx.gbl
    | | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.excel.setup:1145
    | | NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
    | | X-Tomcat-NG: microsoft.public.excel.setup
    | |
    | | Thanks Emily, basically the trick is that you need to use an empty
    | column,
    | | format it as text, copy the data into it, and then you can sort
    properly
    | | using Data | Sort... as you described. You cannot format the column
    | already
    | | containing the data to a "text" column, and that was probably my
    issue...
    | | --
    | | /billd
    | |
    | |
    | | "Emily Lin [MSFT]" wrote:
    | |
    | | > Hi,
    | | >
    | | > What is the expected result after you sort the data?
    | | >
    | | > First, I set the cell format as Text and then copy the data into the
    | cell.
    | | > Thus, 111E6 will not be changed to 1.11E+08.
    | | >
    | | > Based on my testing, in Excel 2000, it will be sorted as the
    following:
    | | > 111E6
    | | > 1237A
    | | > 1237X
    | | > 23476
    | | > 351E3
    | | > 355E2
    | | > 74477
    | | >
    | | > In Excel 2003, when you sort the data and choose the option "sort
    | numbers
    | | > and numbers stored as text separately", it will be sorted as the
    | following,
    | | > same as in Office 2000:
    | | > 111E6
    | | > 1237A
    | | > 1237X
    | | > 23476
    | | > 351E3
    | | > 355E2
    | | > 74477
    | | >
    | | > In Excel 2003, when you sort the data and choose the option "sort
    | anything
    | | > that looks like a number, as a number", it will be sorted as the
    | following:
    | | > 23476
    | | > 355E2
    | | > 74477
    | | > 351E3
    | | > 111E6
    | | > 1237A
    | | > 1237X
    | | >
    | | > Is the situation same on your computer? If not, please write down the
    | | > detail steps which you did and cause what result. And let me know
    your
    | | > expected result.
    | | >
    | | > Also, you can send the specific Excel file to me and let me know what
    | you
    | | > want to do in the Excel file. Thus, we can address the issue more
    | | > efficiently. My Email address is .
    | | >
    | | > Following is the KB article about "Sorting alphanumeric text as
    numeric
    | | > values":
    | | > 214282 Sorting alphanumeric text as numeric values
    | | > http://support.microsoft.com/default.aspx?scid=kb;EN-US;214282
    | | >
    | | > If anything is unclear or if you have any other concerns, please
    don't
    | | > hesitate to contact me.
    | | >
    | | > Happy weekend!
    | | >
    | | > Regards,
    | | >
    | | > Emily Lin
    | | >
    | | > Microsoft Online Partner Support
    | | > Get Secure! - www.microsoft.com/security
    | | > ====================================================
    | | > When responding to posts, please "Reply to Group" via your newsreader
    | so
    | | > that others may learn and benefit from your issue.
    | | > ====================================================
    | | > This posting is provided "AS IS" with no warranties, and confers no
    | rights.
    | | >
    | | >
    | | > --------------------
    | | > | Thread-Topic: Sorting Alphanumeric data in Excel 2003
    | | > | thread-index: Aceu++s170FUIm7EQzOlrR9ngTdOcQ==
    | | > | X-WBNR-Posting-Host: 207.46.193.207
    | | > | From: =?Utf-8?B?YmlsbGQ=?= <>
    | | > | Subject: Sorting Alphanumeric data in Excel 2003
    | | > | Date: Thu, 14 Jun 2007 20:19:00 -0700
    | | > | Lines: 17
    | | > | Message-ID: <>
    | | > | MIME-Version: 1.0
    | | > | Content-Type: text/plain;
    | | > | charset="Utf-8"
    | | > | Content-Transfer-Encoding: 7bit
    | | > | X-Newsreader: Microsoft CDO for Windows 2000
    | | > | Content-Class: urn:content-classes:message
    | | > | Importance: normal
    | | > | Priority: normal
    | | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826
    | | > | Newsgroups: microsoft.public.excel.setup
    | | > | Path: TK2MSFTNGHUB02.phx.gbl
    | | > | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.excel.setup:1141
    | | > | NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
    | | > | X-Tomcat-NG: microsoft.public.excel.setup
    | | > |
    | | > | Trying to sort several part numbers - example
    | | > | 23476
    | | > | 1237X
    | | > | 1237A
    | | > | 355E2
    | | > | 351E3
    | | > | 74477
    | | > | 111E6
    | | > |
    | | > | It will not sort correctly because of the numbers are treated
    | seperately
    | | > and
    | | > | the =TEXT(ref cell, "format") function treats the part #'s with
    "E2"
    | or
    | | > "E3"
    | | > | (basically E#) as scientific notation. Rekeying is not an option,
    as
    | the
    | | > | actual sheet has several thousand of these types of mixed alpha
    | numerics.
    | | > It
    | | > | worked properly in Excel 2002 and Excel 2000, Excel 2003 has
    changed
    | the
    | | > | whole thing!.
    | | > | --
    | | > | /billd
    | | > |
    | | >
    | | >
    | |
    |
    |
     
    Emily Lin [MSFT], Jun 20, 2007
    #5
  6. Hi William,

    The issue is caused that Excel still thinking that the number is a number
    after we format the cell as Text. And then when we sort the cells, it will
    sort the numbers and the texts separately.

    I performed further research and testing. Eventually, I found the solution
    now. : Please refer to the following steps.

    1. In the Excel file, copy the column G.
    2. Open a new notepad file (click the Start menu > Run > type notepad,
    click OK.)
    3. Paste it into the new notepad file.
    4. Press Ctrl + A in the new notepad file. Press Ctrl + C to copy it.
    5. Go to the Excel file, select or insert a blank column (to be used for
    sorting). Format the blank column as Text. Press Ctrl +V to paste the text
    from Notepad to Excel. Thus, the cells are exactly Text now.
    6. Try to sort it again. What is the result?

    If anything is unclear or if you have any other concerns, please don't
    hesitate to contact me.

    Sincerely,

    Emily Lin,
    Microsoft Online Partner Support

    Get Secure! - www.microsoft.com/security
    ======================================================
    When responding to posts, please "Reply to Group" via your newsreader so
    that others may learn and benefit from this issue.
    ======================================================
    This posting is provided "AS IS" with no warranties, and confers no rights.
    ======================================================
     
    Emily Lin [MSFT], Jun 21, 2007
    #6
  7. Hello William,

    Appreciate your update and response.

    I do understand your concerns. From my point of view, I understand your
    feeling and how frustrated when you find that our product cannot meet your
    needs. So, it is my pleasure to help you to reflect your recommendation to
    the proper department for their consideration.

    In addition, please feel free to submit your suggestion on our product to
    the following link. Our Product Group reviews the suggestions submitted by
    our customers. Your feedback is valuable for us to improve our products and
    increase the level of service provided.

    https://support.microsoft.com/common/survey.aspx?scid=sw;en;1208&showpage=1&
    ws=search

    At this moment, I'd like to deliver to you with a simple summary of this
    issue for your reference:

    ARCR
    ***********
    A (Action): sort alphanumeric data in excel 2003
    R (Result): sort result is not as expected
    C (Cause): sort number and text seperately
    R (Resolution): convert the data as TEXT via notepad. And then sort it to
    work around the issue.

    If you have any other questions or concerns, please do not hesitate to
    contact me. It is always my pleasure to be of assistance.

    Have a nice day!

    Best regards,

    Emily Lin

    Microsoft Online Partner Support
    Get Secure! - www.microsoft.com/security
    ====================================================
    When responding to posts, please "Reply to Group" via your newsreader so
    that others may learn and benefit from your issue.
    ====================================================
    This posting is provided "AS IS" with no warranties, and confers no rights.


    --------------------
    | X-Tomcat-ID: 67410156
    | References: <>
    <>
    <>
    <D$>
    <>
    | MIME-Version: 1.0
    | Content-Type: text/plain
    | Content-Transfer-Encoding: 7bit
    | From: (Emily Lin [MSFT])
    | Organization: Microsoft
    | Date: Thu, 21 Jun 2007 05:40:10 GMT
    | Subject: RE: Sorting Alphanumeric data in Excel 2003
    | X-Tomcat-NG: microsoft.public.excel.setup
    | Message-ID: <>
    | Newsgroups: microsoft.public.excel.setup
    | Lines: 36
    | Path: TK2MSFTNGHUB02.phx.gbl
    | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.excel.setup:1179
    | NNTP-Posting-Host: TOMCATIMPORT1 10.201.218.122
    |
    | Hi William,
    |
    | The issue is caused that Excel still thinking that the number is a number
    | after we format the cell as Text. And then when we sort the cells, it
    will
    | sort the numbers and the texts separately.
    |
    | I performed further research and testing. Eventually, I found the
    solution
    | now. : Please refer to the following steps.
    |
    | 1. In the Excel file, copy the column G.
    | 2. Open a new notepad file (click the Start menu > Run > type notepad,
    | click OK.)
    | 3. Paste it into the new notepad file.
    | 4. Press Ctrl + A in the new notepad file. Press Ctrl + C to copy it.
    | 5. Go to the Excel file, select or insert a blank column (to be used for
    | sorting). Format the blank column as Text. Press Ctrl +V to paste the
    text
    | from Notepad to Excel. Thus, the cells are exactly Text now.
    | 6. Try to sort it again. What is the result?
    |
    | If anything is unclear or if you have any other concerns, please don't
    | hesitate to contact me.
    |
    | Sincerely,
    |
    | Emily Lin,
    | Microsoft Online Partner Support
    |
    | Get Secure! - www.microsoft.com/security
    | ======================================================
    | When responding to posts, please "Reply to Group" via your newsreader so
    | that others may learn and benefit from this issue.
    | ======================================================
    | This posting is provided "AS IS" with no warranties, and confers no
    rights.
    | ======================================================
    |
    |
    |
     
    Emily Lin [MSFT], Jun 22, 2007
    #7
  8. Guest

    Guest Guest

    Hi, Emily! I have an intention to do the following. My company product codes
    appear like CAB337-MS-BUL-RD, with variations in the both alphanumerical. I
    had developed custom lists of matching product code and description for my
    department use. It worked perfectly when typing the product code and then
    using the fill handle to enter the product description on the right of each
    cell.

    Next, I would like to make life easy for my colleague so that when they key
    in a letter or beginning letters of the product code, there will be an
    AutoComplete drop down list to pick from. I know how to use 'pick from list'
    feature but my product codes are more than a thousand. Therefore, additional
    AutoComplete will help narrow down the choice from the list (rather than a
    list of thousands of codes).

    Can this be possible? Please assist. Your kind assistance and cooperation
    are much appreciated.

    "Emily Lin [MSFT]" wrote:

    > Hi William,
    >
    > Thanks for your reply.
    >
    > Based on my testing in Excel 2000/2002/2003, the sort results are same.
    > They are as following:
    > 111410
    > 111430
    > 111571
    > 111581
    > 111611
    > 111631
    > 111951
    > ...
    > ...
    > ...
    > 1117A1
    > 1117A2
    > 1117A3
    > 1117E1
    >
    > Do you mean that you can sort the data as you expected in Excel 2000/2002?
    >
    > At this point, please refer to the following KB article to see if you can
    > sort the data as you expected in Excel 2003. I do appreciate your time and
    > efforts on this issue.
    >
    > 322067 How to correctly sort alphanumeric data in Excel
    > http://support.microsoft.com/default.aspx?scid=kb;EN-US;322067
    >
    > If anything is unclear or if you have any other concerns, please don't
    > hesitate to contact me.
    >
    > Sincerely,
    >
    > Emily Lin,
    > Microsoft Online Partner Support
    >
    > Get Secure! - www.microsoft.com/security
    >
    > ======================================================
    > When responding to posts, please "Reply to Group" via your newsreader so
    > that others may learn and benefit from this issue.
    > ======================================================
    > This posting is provided "AS IS" with no warranties, and confers no rights.
    > ======================================================
    >
    > --------------------
    > | X-Tomcat-ID: 53909737
    > | References: <>
    > <>
    > <>
    > | MIME-Version: 1.0
    > | Content-Type: text/plain
    > | Content-Transfer-Encoding: 7bit
    > | From: (Emily Lin [MSFT])
    > | Organization: Microsoft
    > | Date: Mon, 18 Jun 2007 05:59:08 GMT
    > | Subject: RE: Sorting Alphanumeric data in Excel 2003
    > | X-Tomcat-NG: microsoft.public.excel.setup
    > | Message-ID: <D$>
    > | Newsgroups: microsoft.public.excel.setup
    > | Lines: 190
    > | Path: TK2MSFTNGHUB02.phx.gbl
    > | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.excel.setup:1150
    > | NNTP-Posting-Host: TOMCATIMPORT1 10.201.218.122
    > |
    > | Hi William,
    > |
    > | Thanks for your reply and the Excel attachment.
    > |
    > | First, I would like to clarify that:
    > |
    > | The situation (Set column J as Text > Column G is Number > copy column G
    > to
    > | column J > it is also Number in Column J) is normal. It is because that
    > the
    > | cell format is also copied when you copy/paste in Excel. In my first
    > | response, I said that I set cell format as Text and then copy the data
    > from
    > | Notepad to Excel. So, the cell format is still Text.
    > |
    > | If you want to keep the column J as Text when pasting, please copy column
    > G
    > | > then right click Column J > choose Paste Special > choose Value to only
    > | copy value into it. Thus, the cell format is still Text.
    > |
    > | Based on my testing on your Excel file, I can sort it properly. Following
    > | is my testing. If you still cannot sort it on your computer, please let
    > me
    > | know the detail steps you did and what result you encountered.
    > |
    > | 1. Select Column G and sort it. And choose option 2 "sort numbers and
    > | numbers stored as text separately". It is sorted as you want.
    > |
    > | 2. Select Column G > right click it and choose "Format Cells" > choose
    > | Text, click OK. Sort it again and choose option 2 "sort numbers and
    > numbers
    > | stored as text separately". It is sorted as you want.
    > |
    > | If anything is unclear or if you have any other concerns, please don't
    > | hesitate to contact me.
    > |
    > | Sincerely,
    > |
    > | Emily Lin,
    > | Microsoft Online Partner Support
    > |
    > | Get Secure! - www.microsoft.com/security
    > |
    > | ======================================================
    > | PLEASE NOTE: The partner managed newsgroups are provided to assist with
    > | break/fix issues and simple how to questions.
    > |
    > | We also love to hear your product feedback!
    > | Let us know what you think by posting
    > | from the web interface: Partner Feedback
    > | from your newsreader:
    > | microsoft.private.directaccess.partnerfeedback.
    > | We look forward to hearing from you!
    > | ======================================================
    > | When responding to posts, please "Reply to Group" via your newsreader so
    > | that others may learn and benefit from this issue.
    > | ======================================================
    > | This posting is provided "AS IS" with no warranties, and confers no
    > rights.
    > | ======================================================
    > |
    > | --------------------
    > | | Thread-Topic: Sorting Alphanumeric data in Excel 2003
    > | | thread-index: AcevSZc0o7rVplEOTpqfMbf7Je8gBg==
    > | | X-WBNR-Posting-Host: 207.46.192.207
    > | | From: =?Utf-8?B?YmlsbGQ=?= <>
    > | | References: <>
    > | <>
    > | | Subject: RE: Sorting Alphanumeric data in Excel 2003
    > | | Date: Fri, 15 Jun 2007 05:35:00 -0700
    > | | Lines: 125
    > | | Message-ID: <>
    > | | MIME-Version: 1.0
    > | | Content-Type: text/plain;
    > | | charset="Utf-8"
    > | | Content-Transfer-Encoding: 7bit
    > | | X-Newsreader: Microsoft CDO for Windows 2000
    > | | Content-Class: urn:content-classes:message
    > | | Importance: normal
    > | | Priority: normal
    > | | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826
    > | | Newsgroups: microsoft.public.excel.setup
    > | | Path: TK2MSFTNGHUB02.phx.gbl
    > | | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.excel.setup:1145
    > | | NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
    > | | X-Tomcat-NG: microsoft.public.excel.setup
    > | |
    > | | Thanks Emily, basically the trick is that you need to use an empty
    > | column,
    > | | format it as text, copy the data into it, and then you can sort
    > properly
    > | | using Data | Sort... as you described. You cannot format the column
    > | already
    > | | containing the data to a "text" column, and that was probably my
    > issue...
    > | | --
    > | | /billd
    > | |
    > | |
    > | | "Emily Lin [MSFT]" wrote:
    > | |
    > | | > Hi,
    > | | >
    > | | > What is the expected result after you sort the data?
    > | | >
    > | | > First, I set the cell format as Text and then copy the data into the
    > | cell.
    > | | > Thus, 111E6 will not be changed to 1.11E+08.
    > | | >
    > | | > Based on my testing, in Excel 2000, it will be sorted as the
    > following:
    > | | > 111E6
    > | | > 1237A
    > | | > 1237X
    > | | > 23476
    > | | > 351E3
    > | | > 355E2
    > | | > 74477
    > | | >
    > | | > In Excel 2003, when you sort the data and choose the option "sort
    > | numbers
    > | | > and numbers stored as text separately", it will be sorted as the
    > | following,
    > | | > same as in Office 2000:
    > | | > 111E6
    > | | > 1237A
    > | | > 1237X
    > | | > 23476
    > | | > 351E3
    > | | > 355E2
    > | | > 74477
    > | | >
    > | | > In Excel 2003, when you sort the data and choose the option "sort
    > | anything
    > | | > that looks like a number, as a number", it will be sorted as the
    > | following:
    > | | > 23476
    > | | > 355E2
    > | | > 74477
    > | | > 351E3
    > | | > 111E6
    > | | > 1237A
    > | | > 1237X
    > | | >
    > | | > Is the situation same on your computer? If not, please write down the
    > | | > detail steps which you did and cause what result. And let me know
    > your
    > | | > expected result.
    > | | >
    > | | > Also, you can send the specific Excel file to me and let me know what
    > | you
    > | | > want to do in the Excel file. Thus, we can address the issue more
    > | | > efficiently. My Email address is .
    > | | >
    > | | > Following is the KB article about "Sorting alphanumeric text as
    > numeric
    > | | > values":
    > | | > 214282 Sorting alphanumeric text as numeric values
    > | | > http://support.microsoft.com/default.aspx?scid=kb;EN-US;214282
    > | | >
    > | | > If anything is unclear or if you have any other concerns, please
    > don't
    > | | > hesitate to contact me.
    > | | >
    > | | > Happy weekend!
    > | | >
    > | | > Regards,
    > | | >
    > | | > Emily Lin
    > | | >
    > | | > Microsoft Online Partner Support
    > | | > Get Secure! - www.microsoft.com/security
    > | | > ====================================================
    > | | > When responding to posts, please "Reply to Group" via your newsreader
    > | so
    > | | > that others may learn and benefit from your issue.
    > | | > ====================================================
    > | | > This posting is provided "AS IS" with no warranties, and confers no
    > | rights.
    > | | >
    > | | >
    > | | > --------------------
    > | | > | Thread-Topic: Sorting Alphanumeric data in Excel 2003
    > | | > | thread-index: Aceu++s170FUIm7EQzOlrR9ngTdOcQ==
    > | | > | X-WBNR-Posting-Host: 207.46.193.207
    > | | > | From: =?Utf-8?B?YmlsbGQ=?= <>
    > | | > | Subject: Sorting Alphanumeric data in Excel 2003
    > | | > | Date: Thu, 14 Jun 2007 20:19:00 -0700
    > | | > | Lines: 17
    > | | > | Message-ID: <>
    > | | > | MIME-Version: 1.0
    > | | > | Content-Type: text/plain;
    > | | > | charset="Utf-8"
    > | | > | Content-Transfer-Encoding: 7bit
    > | | > | X-Newsreader: Microsoft CDO for Windows 2000
    > | | > | Content-Class: urn:content-classes:message
    > | | > | Importance: normal
    > | | > | Priority: normal
    > | | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826
    > | | > | Newsgroups: microsoft.public.excel.setup
    > | | > | Path: TK2MSFTNGHUB02.phx.gbl
    > | | > | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.excel.setup:1141
    > | | > | NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
    > | | > | X-Tomcat-NG: microsoft.public.excel.setup
    > | | > |
    > | | > | Trying to sort several part numbers - example
    > | | > | 23476
    > | | > | 1237X
    > | | > | 1237A
    > | | > | 355E2
    > | | > | 351E3
    > | | > | 74477
    > | | > | 111E6
    > | | > |
    > | | > | It will not sort correctly because of the numbers are treated
    > | seperately
    > | | > and
    > | | > | the =TEXT(ref cell, "format") function treats the part #'s with
    > "E2"
    > | or
    > | | > "E3"
    > | | > | (basically E#) as scientific notation. Rekeying is not an option,
    > as
    > | the
    > | | > | actual sheet has several thousand of these types of mixed alpha
    > | numerics.
    > | | > It
    > | | > | worked properly in Excel 2002 and Excel 2000, Excel 2003 has
    > changed
    > | the
    > | | > | whole thing!.
    > | | > | --
    > | | > | /billd
     
    Guest, Oct 23, 2007
    #8
  9. Guest

    kmccabe56

    Joined:
    Aug 2, 2012
    Messages:
    1
    Likes Received:
    0
    I have tried following the suggestions posted without success.

    My column has numbers with one to four digits in them, some of which have an alpha character at the end, i.e. 1,2,3,3A1,10,11,12A,100,101,102A,1050,1100A and so on.

    I would like to sort to achieve the results shown above but am unable to do so.
     
    kmccabe56, Aug 2, 2012
    #9
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. RECW

    Sorting when data is on 2 rows???

    RECW, Sep 26, 2003, in forum: Microsoft Excel Setup
    Replies:
    1
    Views:
    488
    AC_VID
    Oct 18, 2003
  2. Guest

    Sorting data

    Guest, Nov 28, 2006, in forum: Microsoft Excel Setup
    Replies:
    3
    Views:
    248
    Gord Dibben
    Nov 28, 2006
  3. Guest

    sorting data from one tab on another

    Guest, May 1, 2007, in forum: Microsoft Excel Setup
    Replies:
    1
    Views:
    224
    Guest
    May 1, 2007
  4. Ramez
    Replies:
    1
    Views:
    264
    Roger Govier
    Aug 15, 2008
  5. A. Beare

    Sorting data on two sheets

    A. Beare, Mar 12, 2009, in forum: Microsoft Excel Setup
    Replies:
    1
    Views:
    270
    Roger Govier
    Mar 12, 2009
Loading...

Share This Page