User Profile
trs1111
Brass Contributor
Joined 2 years ago
User Widgets
Recent Discussions
Sorting Scientific Notation, Alpha, Numeric, and Alphanumeric
Spent much time looking for a way to sort this data set (which is in an Excel worksheet table as part of a much larger dataset) such that it's in order from A to Z and of course all of the rows in the table sort accordingly. Attempted to: 1. Format the column at "Text" and manually add each string, once cell at a time. 2. Format the column as "Custom" and selected custom format "000" and entered each string manually as well as copy & paste. 3. Use "Text to Columns" with the column containing the data set as the source. This is how the data set appears after formatting the column as "Text", manually entering each string, and sorting A-Z. Notice that they strings are NOT properly sorted. Scientific Notation seems to be problematic (see string 7E1) and numeric stings are not properly indexed and interleaved with alphanumeric strings: 011 023 034 7E1 085 107 229 270 360 420 501 627 901 09G 0Z5 39Z 3G5 41N 55Q 6DZ 7FF 82A 98E 99R Interestingly enough, the Excel "Filter" dropdown shows the dataset very nicely indexed in the proper order. Any ideas on how to accomplish the same as the indexing appears in the Filter dropdown? The actual dataset is MUCH larger than the sample set illustrated here. Any assistance would be greatly appreciated! PS Using formulas probably (?) won't work because the dataset is in a large table and is just one of the many dynamic sort criteria. In other words, sometimes the table might be sorted on this dataset, other times it may be sorted on other columns, or there might be multiple sort criteria. It's not a matter of just sorting the data for a single instance.
