Forum Discussion
How to order numbers by whole number, not just first digit of number
I work in archaeology, and have a document to organize some artifacts we found during a recent assessment. The artifacts are labelled according to the initials of the technician who found them, and in the order they were found. For example, items found by John Smith are labelled JS1, JS2, JS3, items by Jane Doe labelled JD1, JD2, JD3, and so on.
I've exported my data into an excel file, but have run into the problem that Excel is ordering them by the first digit of the number, rather than by the whole number. So, rather than listing the data as JS1, JS2, JS3... JS10, JS11, it is ordering them by the first digit, so JS1, JS10, JS11, JS2, JS3.
Is there any way I can get Excel to organize these items by the whole number, rather than just the first digit? I know that if I were to place a zero in there, to change the numbers to JS01, JS02, then it would be able to place them in numerical order, but I am trying to avoid this (all of the physical artifacts are already labelled JS1, JS2, etc, so if I change their numbers in the program to JS01, JS02, that would mean a few hours of work to re-label the items in real life).
2 Replies
=LEFT(E3,2)
=NUMBERVALUE(RIGHT(E3,LEN(E3)-2))
In the attached file i entered above formulas to split the labelling into two columns. This works as long as the initials of the technician are 2 digits. For example John Doe is JD or Mike Smith is MS. 3 digits would lead to a mistake in this example however.
After entering formulas in cells C3 and D3 and copying down i applied individual sort for range C3:E34. The data is sorted by column C (A to Z) and then by column D (ascending) as shown in the picture in the attached file.
It would be better to store the initials and the sequence number in separate columns, but with the current setup you can extract them into two helper columns:
The formula in B2 is =LEFT(A2,2) and that in C2 is =--MID(A2,3,10)
Fill down from B2:C2.
You can then sort the entire range on Initials, then on Sequence Number:
Result:
