View original (French)

Excel: Sorting Numbers WITH Letters

Solved
patchouli Posted messages 64 Status Member -  
 Panrahk -
Hello everyone,

I would like to sort lists of folders in alphanumeric order. So far, no problems...

However, my folder names consist of a number followed by a letter, e.g., 16B, 223A, 223B, 1018A, ...
When I use the "classic" sorting method, Excel sorts them like this: 1018A, 16B, 223A, ...

I would like it to sort them by placing 1018 after 223 (1018>223) without having to put the letter in a separate column. I have several thousand folders, and I don't want to start nitpicking to modify all the numbers with the risks of errors that this implies.

Is it possible? Does anyone have a solution for me?

Thank you in advance
Configuration: Windows XP Firefox 3.0.1

7 answers

  1.
    wilfried_42 Posted messages 912 Status Contributor 245
     
    Hello

    The problem is that you want to sort alphabetic values as numbers, for that you need to format your value at the input stage

    Otherwise, you can add a special sorting column and use a formula that you drag down
    =right(" " & A2;10)

    Adapt the A2 based on the first code

    --
    Best regards
    Wilfried
    2
  2. Zeus54 Posted messages 174 Status Member 90
     
    Hello, indeed Excel considers your cells as text and not as numbers as you wish.
    The only suggestion I would have would be to insert a column. You can insert this formula considering that the folder name is in B, and that they all contain a single letter.

    =CNUM(LEFT(B1,LEN(B1)-1))

    Then, you need to do an initial sort starting from column B (this allows you to put 223A before 223B) and then perform a second sort starting from column A to place 1018 after 223.

    --

    Intelligence is like a parachute,
    when you don't have one, you crash...
    0
  3. patchouli Posted messages 64 Status Member 7
     
    Sure thing. Thank you very much!

    Could you explain a bit about the formulas used? I wasn't familiar with them.

    Otherwise, thanks again to both of you.
    0
  4. Zeus54 Posted messages 174 Status Member 90
     
    You're welcome, and don't forget to mark this topic as "resolved".

    Cnum(text) converts a text string into a number.
    left(text; number of characters): extracts the specified number of characters from the left.
    len(text): returns the number of characters in a text string.

    Generally, with Excel, you can also "insert" a function. A box will then open with a description of the function.

    a+

    --

    Intelligence is like a parachute; when you don't have one, you crash...
    0
  6. patchouli Posted messages 64 Status Member 7
     
    Super!
    0
  7. Panrahk
     
    Thanks for this tip! I had to sort some equipment with references "1c," "32c," "100c" and the "1c" was placed after the "100c"... now it's much better :)
    0
  8. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Good evening,

    and if you want to sort with a single sort you can put in the additional column:
    =CNUM(LEFT(A1,LEN(A1)-1))*100+CODE(RIGHT(A1,1))
    which is case-sensitive
    or
    =CNUM(LEFT(A1,LEN(A1)-1))*100+CODE(UPPER(RIGHT(A1,1)))
    if you want it to be case-insensitive

    eric
    -4