Sorting does numbers in weird order

  • Thread starter Thread starter schmitty_k
  • Start date Start date
S

schmitty_k

I was given a worksheet that I need to sort in numerical
order. The cells contain both letters and numbers (e.g.,
VL 1087). The problem is that it sorts them like VL 1, VL
10, VL 100, VL 1000, VL 1001, etc. VL 16 is after 159 etc.
Kind of like dewey decimal system without the decimals. I
have tried reformatting the cells to text, numbers,
general. Nothing seems to work.

Any ideas?
 
The easiest way is to insert two temporary helper columns next to th
numbers column. Then - seperate the VL from the numbers, using Data
Text-to-columns. Now you have a number column you can sort by, an
after sorting you can delete the two helper columns
 
Schmitty,

I would do this with two helper columns. Assuming that the data has a space
between the letters and the numbers and with the data in column A, insert
two new columns B & C. In B2 enter

=LEFT(A2,FIND(" ",A2))

in C2 enter

=--(RIGHT(A2,LEN(A2)-FIND(" ",A2)))

and copy down both formulas. Now highlight the data in columns A, B & C and
sort by column B and then by Column C then delete the helper columns.

HTH

Sandy
 
Back
Top