1.    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.    02 Sep 2017 #2
    Join Date : Jan 2014
    Posts : 1,393
    Windows 10 Pro (32-bit) 16299.15

    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.    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.    02 Sep 2017 #4
    Join Date : Aug 2014
    Forever West
    Posts : 3,926
    Win10 Home and Pro, Win10 Insider Preview, Win7 Home, Linux Mint

    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.    03 Sep 2017 #5
    Join Date : Jan 2014
    Posts : 1,393
    Windows 10 Pro (32-bit) 16299.15

    Quote Originally Posted by Tony Vella 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
    Join Date : Dec 2015
    Posts : 5,972
    Windows10

    Quote Originally Posted by Tony Vella 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.    03 Sep 2017 #7
    Join Date : Aug 2016
    Posts : 82
    Windows 7-pro-sp1 and windows 10-pro-1703

    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

 


Similar Threads
Thread Forum
Sorting photos
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...
Customization
Gaming monitor blurry text - sort of - text shadowed behind
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...
General Support
Solved Increase text length (prevent text wrapping) under Desktop icons?
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...
Customization
Solved Would like to change the text highlight color in text-entry fields
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...
Customization
Free OCR: Turn a picture of text into real text without spending a dim
Free OCR: Turn a picture of text into real text without spending a dime | PCWorld
Software and Apps
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 21:02.
Find Us
Twitter Facebook Google+ Ten Forums iOS App Ten Forums Android App



Windows 10 Forums