Excel: Sorting Numbers WITH Letters
Solved
patchouli
Posted messages
64
Status
Membre
-
Panrahk -
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
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 réponses
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
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
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...
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...
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.
Could you explain a bit about the formulas used? I wasn't familiar with them.
Otherwise, thanks again to both of you.
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...
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...
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 :)
