Sorting Question

[gatorfe]

A user is trying to get the list below sorted. 
Row 1. 11" RED
Row 2. 12.0" RED
Row 3. 13.0" RED
Row 4. 4 1/2" RED
Row 5. 4 1/4" RED
Row 6. 4 3/4" RED
Row 7. 4.0" RED

The issue is that we want what shows up in row 1,2,3 to be after line 7. 

Also the order with the halfs should be row 7, 5, 4, and 6.  Is there any way to do this type of sorting?  Thank you in advance for any suggestions.

[BigChris]

There isn't an obvious way, but I imagine you could construct something that would let you do it...but it would assume that the format was always the same. You could find the position of the "character and take the preceding characters as the length. You could then look for the / character and convert the fractions into decimals. To do that you'd need to know if the fractions are only always single character divided by single character...do you ever have 11/16" for example? Then you'll need to convert the integer element from a string to a number and add it to the fraction element

Once you've done that you would be able to sort your list by the length first, followed b the colour.

I wouldn't fancy trying it myself, but I can see how it might be possible...good luck!

[rih9000]

I would use the SUBSTRING function and look for the first occurrence of a blank or decimal point,
then convert the stuff to the left of it to a numeric integer,
and convert the stuff to the right of it to a fractional number (stopping where you see the "inches" symbol ( " ).

I made a YouTube video on how to use the SUBSTRING function here:

https://www.youtube.com/watch?v=clrwlFuDfMI&feature=youtu.be

8)
rih9000