Sorting Numbers with a "-" in it
Probably my incompetence here but i have a confusing issue at the minute. I have a long list of job numbers and am trying to sort them in numerical order.
There is a criteria that we use to create these that you should probably be aware of:
- Primary number is a 5 digit numerical number (i.e.21495)
- If there is multiple jobs to the identical specification we use the same primary and add -1, -2 etc. (i.e. 21495-1, 21495-2 etc...)
- If a customer rings up claiming warranty on the job, we add "W" to the end of the new number (I.e. 21495W or 21495-1W)
- If we sell a parts order, we add "P" to the end, or "F" represents an internal fastfit process
If i do a standard sort on the job number column, it just puts everything with a "-" or a letter to the bottom of the list, and sorts each section in order.
I then seperated the number, so i had the primary number split out, and then ran a sort on the second column, and then on the first column. However if there was more than 9 variations of the same job (i.e. soon as i get to 21495-10), it sorts it by the first digit, then the second, etc...
The formula that i used for the above is:
=IF(FIND("-", [Job No]1) > 0, VALUE(MID([Job No]1, 1, 5)), [Job No]1)
Is there a way to overcome this? See below screen capture, and also a link to a dummy sheet-will obviously need to request access... (ignore the 3rd column!!)
Help Article Resources
Check out the Formula Handbook template!