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...b;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: Sorting Alphanumeric data in Excel 2003
| X-Tomcat-NG: microsoft.public.excel.setup
| Message-ID:
| 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: ?B?YmlsbGQ=?=
| | References:
|
| | Subject: 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...b;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: ?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
| | |
| |
| |
| |
|
|