Sorting 4 or 5 digits numbers with text


I have a serial numbers from 1 to 10000 which are mixed up with numbers with text... i am not able to sort. Tried text formula to create all the numbers in 5 digits but some numbers with text doesnt change. Pls could you tell me how to sort with any formulas..


F-11836    F-11837   



F-1185     F-11851    F-11852    F-11853    F-11858    F-11859    F-1186    



Simply put: they can't be sorted. Therefore you will have to change the numbers. There are two ways.

  1. Create conditions to sort by numbers
    If the prefix is always F- you can move the prefix into cell formatting and keep only the number as the cell's value. For example, apply a Custom cell format like 
    "F-"00000 to the cell and enter only the number. E.g. if you enter 381 in the cell it will be displayed as F-00381. You can change your existing data using a formula like =ABS(MID(A2,3,10)) which would extract the number, discarding the "F-". The ABS() function will both, convert the extracted string to a number and do away with occasional negative results that might be caused by stray blank spaces in the number field.
  2. Create conditions to sort by text
    If you have different prefixes or must prepare for having them in the future thre numbers must be changed to text. Use a formula like ="F-" & TEXT(ABS(MID(A2,3,10)),"00000") which will convert the existing number to a sortable string.

Answer the Question

You must create an account to use the forum. Create an Account or Login