Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Sorting 4 or 5 digits numbers with text

0

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..

Example:

F-11836    F-11837   

F-1184

F-1184C1 

F-1185     F-11851    F-11852    F-11853    F-11858    F-11859    F-1186    
excel
MS Office Version: 2013
posted by: Arun_86 on Sun Aug 23, 2020 at 12:19 pm
Answer
Discuss

Answers

0

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.
posted by: Variatus (rep: 4889) on Sun Aug 23, 2020 at 8:14 pm
Discuss


Answer the Question

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