Larger sizes against smaller sizes

**nathanocs** · 08-13-2013, 12:57 AM

How do i get Excel to recognize that 20ltrs is bigger than 4ltrs when i sort it? i have tried using number format but it still doesn't work.

**FDibbins** · 08-13-2013, 01:03 AM

Hi and welcome to the forum

It sorts them "funky" because they are not values, they are text. If you really have to have the unit of measure added (it would be simple if you didnt), you could probably add a helper column to extract the value =left(a1,len(a1)-3)*1...assuming the unit of measure will always be 3 letters???), and then base the sort on that column?

**Rambo4711** · 08-13-2013, 04:06 AM

Or split the data into two cells.

Mark the column with your volumes. Use Find and replace "l" with " l" (blank+l)
Insert an empty column at right of the volumes.
Mark the column with your volumes. Choose "Text to columns" and mark "Blank" as delimiter

**Ace_XL** · 08-13-2013, 04:10 AM

If ltrs is all youve got, format all cells as

#"ltrs"

Then you need to input only the number and can sort the data

**nathanocs** · 08-13-2013, 04:41 PM

hey guys, thanks for the answers but im still confused, please see attachment and hopefully you get a better understanding of my predicament. I want to first sort by PRODUCT alphabetically the by SIZE smallest to largest.