Sort combination of letters and numbers

  • Thread starter Thread starter Millabird
  • Start date Start date
M

Millabird

I have this type of data to sort: C-1, C-2, C-3, C-4, C-5, C-6, all the way
up to C-500, but it doesn't sort into number order. How can I make it sort
by number, and disregard the "C-" part? thanks!
 
Assuming your data is in column C and it starts in row 1...
In a blank column next to your data enter this formula...
=RIGHT(C1,LEN(C1)-2)
Fill the formula down to the bottom of your data.
Select both columns and sort using the new column as the key.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins - try the "Special Sort" free trial - no registration)



in message
I have this type of data to sort: C-1, C-2, C-3, C-4, C-5, C-6, all the way
up to C-500, but it doesn't sort into number order. How can I make it sort
by number, and disregard the "C-" part? thanks!
 
You will need a 'helper' column with the number extracted
If the C-nnnn is in A1, use =--MID(A1,3,255) to get nnn
best wishes
 
Back
Top