Sorting variable length numbers, some with letters on the end

[ROCKE]

Column B lists my part numbers that are 3, 4, 5 and 6 digit long, some with letters on the end. They are formatted as numbers with the exception of numbers with leading zeros (00023).
I would like to sort using the first digit of the item number as such...

1000
110
110300
11040
11040S

...but i get this

110
1000
11040
110300
11040S

 

[PatOBrien198]

I believe that you'll need to format the numbers as text, then sort on the text column....

If the following formula is in column K
=TEXT(J10,"#")
then you sort both column J and K, using K as the sort field, and click the option for "Sort numbers and numbers stored as text separately"

 

[ROCKE]

Thank you. Worked perfectly.