 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Setup / October 2007Sorting Alphanumeric data in Excel 2003
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!.  Signature/billd 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 v-emilyl@microsoft.com. 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== [quoted text clipped - 32 lines] | (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!. 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... Signature/billd > Hi, > [quoted text clipped - 105 lines] > | worked properly in Excel 2002 and Excel 2000, Excel 2003 has changed the > | whole thing!. 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=?= <daffyd@newsgroup.nospam> | References: <4EB13D30-C4F7-46B5-9537-DEABDC13C023@microsoft.com> <boxNX3xrHHA.3656@TK2MSFTNGHUB02.phx.gbl> | Subject: RE: Sorting Alphanumeric data in Excel 2003 | Date: Fri, 15 Jun 2007 05:35:00 -0700 [quoted text clipped - 48 lines] | > | > 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 [quoted text clipped - 77 lines] | > | worked properly in Excel 2002 and Excel 2000, Excel 2003 has changed the | > | whole thing!. 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: <4EB13D30-C4F7-46B5-9537-DEABDC13C023@microsoft.com> <boxNX3xrHHA.3656@TK2MSFTNGHUB02.phx.gbl> <101EDE77-E45C-4CDD-B265-704F1954136F@microsoft.com> | MIME-Version: 1.0 | Content-Type: text/plain [quoted text clipped - 220 lines] | the | | > | whole thing!. 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. ====================================================== 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: <4EB13D30-C4F7-46B5-9537-DEABDC13C023@microsoft.com> <boxNX3xrHHA.3656@TK2MSFTNGHUB02.phx.gbl> <101EDE77-E45C-4CDD-B265-704F1954136F@microsoft.com> <D$pzM3WsHHA.2528@TK2MSFTNGHUB02.phx.gbl> <y0HsYLysHHA.2312@TK2MSFTNGHUB02.phx.gbl> | MIME-Version: 1.0 | Content-Type: text/plain [quoted text clipped - 45 lines] | This posting is provided "AS IS" with no warranties, and confers no rights. | ====================================================== 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. > Hi William, > [quoted text clipped - 288 lines] > | the > | | > | whole thing!. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.