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: <
[email protected]>
<
[email protected]>
<
[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
| From: (e-mail address removed) (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: <
[email protected]>
| 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=?= <
[email protected]>
| | References: <
[email protected]>
| <
[email protected]>
| | Subject: RE: Sorting Alphanumeric data in Excel 2003
| | Date: Fri, 15 Jun 2007 05:35:00 -0700
| | Lines: 125
| | Message-ID: <
[email protected]>
| | 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 (e-mail address removed).
| | >
| | > 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=?= <
[email protected]>
| | > | Subject: Sorting Alphanumeric data in Excel 2003
| | > | Date: Thu, 14 Jun 2007 20:19:00 -0700
| | > | Lines: 17
| | > | Message-ID: <
[email protected]>
| | > | 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
