Forum Discussion
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 th...
Not familiar with the tool and not sure I could make it work.
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 FunctionPress 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)
- Thanks. I tried it in my workbook, but an not that adept at VBA...so it didn't work.
Alternative for O365:
=LET( ζ, TEXTSPLIT(A1, ";", , 1), γ, ISERR(FIND("-", ζ)), ξ, 0 + IF(γ, "0 " & ζ, SUBSTITUTE(ζ, "-", " ")), TEXTJOIN(";", , SORTBY(ζ, ξ)) )yes I tried something like that (much more simple than my post above) but I had problems if a whole number was used. For example if the cell has: 1/2;1-1/8;22-12/18;1/8;1/22;2;
the 2 at the end doesn't sort right and if you don't add the "0 " then the fractions try to be dates. maybe someone knows another way to force a fraction to be a fraction number and not a date.JosWoolley here is a variation that can handle the whole number:
=LET( fracs, TEXTSPLIT(A1, ";", , 1), vals, --IF(ISERR(FIND("-", fracs)), IFERROR(--( "0 " & fracs),fracs), SUBSTITUTE(fracs, "-", " ")), TEXTJOIN(";", , SORTBY(fracs, vals)) )
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.
williamdhicks If you have Excel 365 you can use this formula:
=LET(in,A1, whole_fractions,TEXTSPLIT(in,"-",";",1,,0), values,IF(ISNUMBER(SEARCH("/",whole_fractions)),--TEXTBEFORE(whole_fractions,"/")/--TEXTAFTER(whole_fractions,"/"),--whole_fractions), TEXTJOIN(";",1,TAKE(SORT(HSTACK(TAKE(values,,1)+TAKE(values,,-1),TEXTSPLIT(in,,";"))),,-1)) )EDIT: after looking at HansVogelaar code I made the formula a little easier:
=LET(in,A2, whole_fractions,TEXTSPLIT(in,"-",";",1,,0), values,--IF(ISNUMBER(SEARCH("/",whole_fractions)),"0 "&whole_fractions,whole_fractions), TEXTJOIN(";",1,TAKE(SORT(HSTACK(TAKE(values,,1)+TAKE(values,,-1),TEXTSPLIT(in,,";"))),,-1)) )
