Forum Discussion

williamdhicks's avatar
williamdhicks
Copper Contributor
Feb 06, 2023

Sort fractions within a cell in Excel

I am trying to sort data within a cell (not a column/row) ...and it's in fractions. 

As an example, I have this in one cell:

1/2;1-1/8;22-12/18;1/8;1/22;


I want it reorder the data in the correct small to larger order to this:

1/22;1/8;1/2;1-1/8;22-12/18;

 

Is there a formula to do this (not a text to cell, or 

excel
    • williamdhicks's avatar
      williamdhicks
      Copper Contributor
      Feb 06, 2023
      Not familiar with the tool and not sure I could make it work.
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Feb 06, 2023

        williamdhicks 

        I have attached a sample workbook. It is a macro-enabled workbook so you'll have to allow macros when you open it.

        The VBA code:

        Function SortFractions(ByVal s As String) As String
    Dim sc As Boolean
    Dim f1() As String
    Dim f2() As Double
    Dim m As Long
    Dim i As Long
    Dim j As Long
    Dim t1 As String
    Dim t2 As Double
    s = Trim(s)
    If Right(s, 1) = ";" Then
        sc = True
        s = Left(s, Len(s) - 1)
    End If
    f1 = Split(s, ";")
    m = UBound(f1)
    ReDim f2(m)
    For i = 0 To m
        f2(i) = Evaluate(Replace(f1(i), "-", " "))
    Next i
    For i = 0 To m - 1
        For j = i + 1 To m
            If f2(i) > f2(j) Then
                t1 = f1(i)
                f1(i) = f1(j)
                f1(j) = t1
                t2 = f2(i)
                f2(i) = f2(j)
                f2(j) = t2
            End If
        Next j
    Next i
    SortFractions = Join(f1, ";") & IIf(sc, ";", "")
End Function

        Press Alt+F11 to view the code.

        With a string with multiple fractions in A1, the following formula returns the fractions in ascending order:

        =SortFractions(A1)

Resources