Windows 10: MS Excel - Sorting text cells


  1. Posts : 83
    Windows 10 Pro 10.0.17134 Build 17134
       02 Sep 2017 #1

    MS Excel - Sorting text cells


    I am entering stamp collection catalogue "numbers" in a spreadsheet column. However, I am having problems sorting the stamps properly. For example: stamps 1, 2, 3, 1a, 2a, 3a should be sorted as 1, 1a, 2, 2a, 3, 3a. I know I am doing something wrong because I keep getting the original: 1, 2, 3, 1a, 2a, 3a. Could someone please give me a hint or two at how to go about sorting my "numbers" properly? Thanks in advance.
      My ComputerSystem Spec


  2. Posts : 1,452
    Windows 10 Pro (32-bit) 16299.15
       02 Sep 2017 #2

    1. Insert a blank column next to the one with your numbers 1,1a etc
    2. In that new column use a formula which is
      Code:
      =""&A2
      where A2 is the cell reference of the first value in the catalogue numbers column (in my example it's in the first column)
    3. copy that formula down to all rows
    4. Add a title to the top of the new column, eg. 'Sorter'
    5. Select all the data range, and from the Data bit of the Ribbon, select the Sort option (not using an Autofilter), and select to sort on the new Sorter column
    6. You should get a dialog popping up saying the sort key may not sort as expected because it contains some numbers formatted as text. In this, choose the option to 'Sort numbers and numbers stored as text separately'.

    This should sort in the order 1,1a,1b,2,2a etc.
      My ComputerSystem Spec


  3. Posts : 83
    Windows 10 Pro 10.0.17134 Build 17134
    Thread Starter
       02 Sep 2017 #3

    I regret that when you go into double digits your solution will provide: 1, 10, 11, 12 .......1a, 2, 20, 21, .... 2a, etc.
      My ComputerSystem Spec


  4. Posts : 5,152
    Win10 Home and Pro, Win10 Insider Preview, WinXP Home Premium, Linux Mint
       02 Sep 2017 #4

    I've had a similar problem with other things and the sort feature but mostly was solved by adding a 0 at the beginning of the number or maybe 2 0s if it was a long list. Seems Windows or Office likes to sort by number, number & letter, 2 numbers, 2 numbers & letter, etc. I got a nice little free program from Organizer Software by Duck Software for our church's library cataloging which can export as a .csv file, they have one for Stamps.
      My ComputerSystem Spec


  5. Posts : 1,452
    Windows 10 Pro (32-bit) 16299.15
       03 Sep 2017 #5

    Tony Vella said: View Post
    I regret that when you go into double digits your solution will provide: 1, 10, 11, 12 .......1a, 2, 20, 21, .... 2a, etc.
    Yes I see your point. It's quite fiddly to separate numbers out of a string with a mix of numbers and letters.

    Is there always just one letter at the end? Eg. 23k but not 27aa?

    If there's just one letter then something like this formula in the 'Sorter' column might work?

    Code:
    =IF(ISNUMBER(0+RIGHT(A3,1)),TEXT(A3,"00000")&"_",TEXT(LEFT(A3,LEN(A3)-1),"00000")&RIGHT(A3,1))
    It's along the lines of Berton's comment of adding 0s but by putting a non-numeric _ character at the end when there's no letter, it should stay as a non-numeric value so no danger of it losing the leading zeros.

    However this won't work if you ever have multiple letters at the end (eg. 27ab).
      My ComputerSystem Spec

  6.    03 Sep 2017 #6

    Tony Vella said: View Post
    I am entering stamp collection catalogue "numbers" in a spreadsheet column. However, I am having problems sorting the stamps properly. For example: stamps 1, 2, 3, 1a, 2a, 3a should be sorted as 1, 1a, 2, 2a, 3, 3a. I know I am doing something wrong because I keep getting the original: 1, 2, 3, 1a, 2a, 3a. Could someone please give me a hint or two at how to go about sorting my "numbers" properly? Thanks in advance.
    Start at 1A. numbers come before text in lexicographical sorting.
      My ComputerSystem Spec


  7. Posts : 91
    Windows 7-pro-sp1 and windows 10-pro-1803
       03 Sep 2017 #7

    It's a feature, see MS description and attempted solutions here:
    https://support.microsoft.com/en-us/...-data-in-excel
    Worksheet design needs to comply with excel features.
    For stamps you might need several columns such as country, some subdivision, year, something about the stamp. Follow with multicolumn sort.
    Now, if it really is of the 1,2,1a, type, AND if only one lower case letter is used then this mad formula converts letters of what's in column A to a number after a decimal point.
    Multiply makes it a number otherwise excel might still treat it as text.
    IF(ISNUMBER($A2),$A2,LEFT($A2,LEN($A2)-1)&"."&CODE(RIGHT($A2,1))-96)
    Just couldn't resist joining the fun of playing with it :)
    Attached Thumbnails Attached Thumbnails ExcelSortTest.jpg  
      My ComputerSystem Spec


 

Related Threads
Sorting photos in Customization
I have just taken delivery of a new W10 pc and would appreciate advice on sorting my photos. In XP I could download a cameraful of photos and, without having to change their titles, I could click and drag them into any order, not just one dictated...
Hi all, Just bought my first PC in a decade, and I find in some applications or menus the text is distractingly fuzzy. I have a gaming monitor, set my display to 144 hz - please bear in mind I hardly have any idea what I'm doing - and am using a...
I've read references in increasing icon spacing via Regedit tweaks, but will that also increase the text length under icons before the text wraps to a new line. For example a folder named "Screen Grabs" displays both words on one line with Win7 but...
For instance, if I highlight a word in a text-entry field, it's a very bright blue. This is also true in Firefox, Thunderbird, Word, OneNote, various fora, etc. but not in Excel. Prior to installing Windows 10, the highlight in a text-entry...
Free OCR: Turn a picture of text into real text without spending a dime | PCWorld
Our Sites
Site Links
About Us
Windows 10 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 10" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd
All times are GMT -5. The time now is 20:12.
Find Us