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...
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)) )- Ah, yes, didn't consider whole numbers. Good work!
- Not sure my system understand the special characters.
