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 th...
excel
mtarler's avatar
mtarler
Silver Contributor
Feb 07, 2023
OK but you didn't answer the questions. is it safe to assume that the format will always be number SPACE text? might text be 'alone' (meaning the only thing between 2 semicolons) and if so is that a 0 or should be ignored?
williamdhicks's avatar
williamdhicks
Copper Contributor
Feb 08, 2023
Yes, it will likely always be number space text (12 ft), or number dash fraction text (2-1/2 in).
  • mtarler's avatar
    mtarler
    Silver Contributor
    Feb 08, 2023

    williamdhicks Updated formula.  I tried to avoid those latest text functions that you don't appear to have yet:

    =LET(in,A7,
    L, LEN(in),
    n, L-LEN(SUBSTITUTE(in,";","")),
    items, TRIM(MID(SUBSTITUTE(in, ";",REPT(" ",L)),SEQUENCE(n,,,L),L)),
    fracs, LEFT(items,IFERROR(SEARCH(" ",items)-1,99)),
    vals, --IF(ISERR(FIND("-", fracs)),
               IFERROR(--( "0 " & fracs),fracs),
               SUBSTITUTE(fracs, "-", " ")),
    f, FILTER(fracs, ISNUMBER(vals),""),
    v, FILTER(vals, ISNUMBER(vals),""),
    TEXTJOIN(";", , SORTBY(f,v))
 )

     

Resources