Windows 10: MS Excel - Sorting text cells

Discus and support MS Excel - Sorting text cells in Microsoft Office and 365 to solve the problem; I am entering stamp collection catalogue "numbers" in a spreadsheet column. However, I am having problems sorting the stamps properly. For example:... Discussion in 'Microsoft Office and 365' started by Tony Vella, Sep 1, 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.

    :)
     
    Tony Vella, Sep 1, 2017
    #1

  2. How to Edit Word and Excel file in N9

    Hi, I can open the Ms Word and Ms Excel Files in N9 but I can not edit the text or Cells. Please advise the way to edit the text in the Word or Excel Files.
     
    dshiwakoti, Sep 1, 2017
    #2
  3. Storing texts on memory card.

    Thanks for everybody's input. I will jut have to download all my text messages to EXCEL - which I can do using NOKIA PC Suite - and accept that they stuff everything into one cell. This will not permit me to sort them chronologically - but so be it.
     
    interpreter---01, Sep 1, 2017
    #3
  4. DavidY Win User

    MS Excel - Sorting text cells

    • Insert a blank column next to the one with your numbers 1,1a etc
    • In that new column use a formula which is Code: =""&A2[/quote] where A2 is the cell reference of the first value in the catalogue numbers column (in my example it's in the first column)
    • copy that formula down to all rows
    • Add a title to the top of the new column, eg. 'Sorter'
    • 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
    • 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.
     
    DavidY, Sep 1, 2017
    #4
  5. I regret that when you go into double digits your solution will provide: 1, 10, 11, 12 .......1a, 2, 20, 21, .... 2a, etc.
     
    Tony Vella, Sep 2, 2017
    #5
  6. Berton Win User
    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.
     
    Berton, Sep 2, 2017
    #6
  7. DavidY Win User
    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))[/quote] 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).
     
    DavidY, Sep 2, 2017
    #7
  8. cereberus Win User

    MS Excel - Sorting text cells

    Start at 1A. numbers come before text in lexicographical sorting.
     
    cereberus, Sep 2, 2017
    #8
  9. 91fw Win User
    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 *Smile
     
    91fw, Apr 5, 2018
    #9
Thema:

MS Excel - Sorting text cells

Loading...
  1. MS Excel - Sorting text cells - Similar Threads - Excel Sorting text

  2. Excel copy and paste cell to non adjacent cell in Apple

    in Windows 10 Drivers and Hardware
    Excel copy and paste cell to non adjacent cell in Apple: In windows Excel to copy and paste to a non adjacent cell press the control key and move the cusor to the edge of the cell and you get an arrow with a plus sign to move the contents to another cell. On my Apple computer I cannot get an arrow to appear when I select the cell....
  3. MS office Excel 2016

    in Windows 10 Network and Sharing
    MS office Excel 2016: How do I apply a new Theme Font to existing workbooks created with another Theme so that all fonts are changed to the new Theme? https://answers.microsoft.com/en-us/windows/forum/all/ms-office-excel-2016/1465ff54-f342-4038-85bf-30d61a970d98
  4. MS excel 2016 issue

    in Windows 10 Network and Sharing
    MS excel 2016 issue: I have windows 10 with MS Office 2016 on i5 and 8 GB RAM machine but when i try to open a 14 MB excel file it show not responding and after some time stop working. please help...
  5. Make Excel display text in a column's cells based on the presence of text in neighboring cells

    in Windows 10 Network and Sharing
    Make Excel display text in a column's cells based on the presence of text in neighboring cells: 1-21-19 I have seen some discussions relating to this question that involve formulae that result in text appearing in a column's cells based on the findings of a numerical value in a neighboring cell, or the converse-- a numerical value is made to appear based on a formula...
  6. MS Edge - Blurry Text

    in Browsers and Email
    MS Edge - Blurry Text: Decided to give Edge a proper go to see if I could get used to it. I'm actually quite liking it as it transpires, apart from one irritating issue: The text looks very blurry when compared with Chrome. I thought initially it was a "Clear Type" issue, but toggling Clear Type...
  7. Can't Adjust Cell Size in Excel

    in Microsoft Office and 365
    Can't Adjust Cell Size in Excel: I don't know which it might be, but I'm no longer able to adjust the cell size of columns and rows in Excel with a simple double mouse click. Is anyone else having this issue or is it just me? 44640
  8. EXCEL sort sequence

    in Microsoft Office and 365
    EXCEL sort sequence: Hi there I'm trying to create a list of Movies in my DB in alphabetical order with folders A,B,C etc. Is there a way to store for example "The Day the earth Stood Still - 1951" in D ignoring the word "THE" in the sort sequence if it's the first word of the film title. I...
  9. Is default cell width for Excel 2013 8.43 or 8.38?

    in Microsoft Office and 365
    Is default cell width for Excel 2013 8.43 or 8.38?: Hi guys, almost a month ago I was working on a file on Excel 2010 (whose cells were 15 x 8,43), when all of a sudden Windows asked me to install the so-called "Windows 10 anniversary" update and, when I went on to open the file, I found out the cell width had gone from 8.43...
  10. Gaming monitor blurry text - sort of - text shadowed behind

    in Windows 10 Support
    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 DCI-D cable. It looks to...