PC Review


Reply
Thread Tools Rate Thread

Sorting Alphanumeric data in Excel 2003

 
 
=?Utf-8?B?YmlsbGQ=?=
Guest
Posts: n/a
 
      15th Jun 2007
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
 
Reply With Quote
 
 
 
 
Emily Lin [MSFT]
Guest
Posts: n/a
 
      15th Jun 2007
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 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...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: =?Utf-8?B?YmlsbGQ=?= <(E-Mail Removed)>
| Subject: Sorting Alphanumeric data in Excel 2003
| Date: Thu, 14 Jun 2007 20:19:00 -0700
| Lines: 17
| Message-ID: <(E-Mail Removed)>
| 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
|

 
Reply With Quote
 
 
 
 
=?Utf-8?B?YmlsbGQ=?=
Guest
Posts: n/a
 
      15th Jun 2007
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 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...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: =?Utf-8?B?YmlsbGQ=?= <(E-Mail Removed)>
> | Subject: Sorting Alphanumeric data in Excel 2003
> | Date: Thu, 14 Jun 2007 20:19:00 -0700
> | Lines: 17
> | Message-ID: <(E-Mail Removed)>
> | 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
> |
>
>

 
Reply With Quote
 
Emily Lin [MSFT]
Guest
Posts: n/a
 
      18th Jun 2007
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=?= <(E-Mail Removed)>
| References: <(E-Mail Removed)>
<(E-Mail Removed)>
| Subject: RE: Sorting Alphanumeric data in Excel 2003
| Date: Fri, 15 Jun 2007 05:35:00 -0700
| Lines: 125
| Message-ID: <(E-Mail Removed)>
| 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 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...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: =?Utf-8?B?YmlsbGQ=?= <(E-Mail Removed)>
| > | Subject: Sorting Alphanumeric data in Excel 2003
| > | Date: Thu, 14 Jun 2007 20:19:00 -0700
| > | Lines: 17
| > | Message-ID: <(E-Mail Removed)>
| > | 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
| > |
| >
| >
|

 
Reply With Quote
 
Emily Lin [MSFT]
Guest
Posts: n/a
 
      20th Jun 2007
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: <(E-Mail Removed)>
<(E-Mail Removed)>
<(E-Mail Removed)>
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
| From: (E-Mail 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: <D$(E-Mail Removed)>
| 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=?= <(E-Mail Removed)>
| | References: <(E-Mail Removed)>
| <(E-Mail Removed)>
| | Subject: RE: Sorting Alphanumeric data in Excel 2003
| | Date: Fri, 15 Jun 2007 05:35:00 -0700
| | Lines: 125
| | Message-ID: <(E-Mail Removed)>
| | 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 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...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: =?Utf-8?B?YmlsbGQ=?= <(E-Mail Removed)>
| | > | Subject: Sorting Alphanumeric data in Excel 2003
| | > | Date: Thu, 14 Jun 2007 20:19:00 -0700
| | > | Lines: 17
| | > | Message-ID: <(E-Mail Removed)>
| | > | 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
| | > |
| | >
| | >
| |
|
|

 
Reply With Quote
 
Emily Lin [MSFT]
Guest
Posts: n/a
 
      21st Jun 2007
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.
======================================================


 
Reply With Quote
 
Emily Lin [MSFT]
Guest
Posts: n/a
 
      22nd Jun 2007
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...08&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: <(E-Mail Removed)>
<(E-Mail Removed)>
<(E-Mail Removed)>
<D$(E-Mail Removed)>
<(E-Mail Removed)>
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
| From: (E-Mail Removed) (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: <(E-Mail Removed)>
| 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.
| ======================================================
|
|
|

 
Reply With Quote
 
=?Utf-8?B?amF5ZWU=?=
Guest
Posts: n/a
 
      23rd Oct 2007
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...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: <(E-Mail Removed)>
> <(E-Mail Removed)>
> <(E-Mail Removed)>
> | MIME-Version: 1.0
> | Content-Type: text/plain
> | Content-Transfer-Encoding: 7bit
> | From: (E-Mail 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: <D$(E-Mail Removed)>
> | 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=?= <(E-Mail Removed)>
> | | References: <(E-Mail Removed)>
> | <(E-Mail Removed)>
> | | Subject: RE: Sorting Alphanumeric data in Excel 2003
> | | Date: Fri, 15 Jun 2007 05:35:00 -0700
> | | Lines: 125
> | | Message-ID: <(E-Mail Removed)>
> | | 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 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...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: =?Utf-8?B?YmlsbGQ=?= <(E-Mail Removed)>
> | | > | Subject: Sorting Alphanumeric data in Excel 2003
> | | > | Date: Thu, 14 Jun 2007 20:19:00 -0700
> | | > | Lines: 17
> | | > | Message-ID: <(E-Mail Removed)>
> | | > | 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

 
Reply With Quote
 
New Member
Join Date: Aug 2012
Posts: 1
 
      2nd Aug 2012
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.
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting alphanumeric data at 2nd, 3rd or 4th character position kykles Microsoft Excel Misc 5 3rd Jun 2009 12:18 AM
Sorting mixed alphanumeric data coxrail Microsoft Access VBA Modules 6 14th Sep 2008 02:10 PM
Sorting by complex alphanumeric data =?Utf-8?B?U2FuZGll?= Microsoft Access Reports 8 6th Feb 2007 07:29 AM
Help With Sorting Alphanumeric Data =?Utf-8?B?SmF5IE1haXRyaQ==?= Microsoft Access 3 21st Aug 2006 07:22 PM
SORTING ALPHANUMERIC DATA =?Utf-8?B?enl1cw==?= Microsoft Access 1 16th Dec 2005 05:01 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:55 PM.