Sorting Numbers with a "-" in it

grant53396
grant53396 ✭✭
edited 12/09/19 in Formulas and Functions

Hi all,

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!!)

https://app.smartsheet.com/b/home?lx=3m_7C3zu9FINS2JC-OmJvQ 

 

2018-10-31_07-42-35.png

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!