Sort fractions within a cell in Excel

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))
 )

in-cell-sort.xlsx12 KB

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
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
Copper Contributor
Feb 07, 2023
no, that was just a sample, to show the kind of text that might appear. It's not likely to have mixed units.
sometimes it's a list of items--e.g. 1 of this, 2 of that, 3 of that.
If you want something more complicated look at my other question about how to backward out data into what columns it exists in (and create the list of column headers from that). It would be great if that can do look at a range and do that.
mtarler
Silver Contributor
Feb 07, 2023
oooh, now things are getting spicy. lol. So you would need to convert inches to feet (or feet to inches) also right? and is it safe to assume that the format will ALWAYS be number (with or w/o fraction) then SPACE then UNIT? if units do not exist then what?
and you might as well tell if/when a comma might appear as you mention, that is probably the easy part. lol
williamdhicks
Copper Contributor
Feb 07, 2023
1 in;2 ft;1-1/4 in;1 ft; 1-1/8 in
Maybe something like this...it would be rare to have the commas, and those can be fixed easily.
mtarler
Silver Contributor
Feb 07, 2023
"possible fixes", I'm sure. But I need to know exactly what to expect/account for and how they should be handled. some examples would be very helpful. You might also consider the balance between including every possible scenario and the size (and potential computational lag) of the formula. Maybe throw the cases at us and if there is something particularly complicated we can let you know.
williamdhicks
Copper Contributor
Feb 07, 2023
This works great for most of my situations..but since you seem to be an expert in Excel...maybe you can help with these oddball sitatuions>
Sometimes it's a mix with a comma or a blank--that doesn't pull the data (the last value for exampled would not have anything)
....or it has words in the string.
Is there any possible fixes for those situations?

mtarler

Silver Contributor

Feb 07, 2023

williamdhicks As I noted above, I recommend trying to get the latest update and hopefully you will get the new text functions like TEXTSPLIT().

In the meantime you can try this:

=LET(in,A1,
    L, LEN(in),
    n, L-LEN(SUBSTITUTE(in,";","")),
    fracs, TRIM(MID(SUBSTITUTE(in, ";",REPT(" ",L)),SEQUENCE(n,,,L),L)),
    vals, --IF(ISERR(FIND("-", fracs)),
               IFERROR(--( "0 " & fracs),fracs),
               SUBSTITUTE(fracs, "-", " ")),
    TEXTJOIN(";", , SORTBY(fracs, vals))
 )

williamdhicks
Copper Contributor
Feb 07, 2023
Does not have Textsplit--only has Textjoin, Text.
mtarler
Silver Contributor
Feb 07, 2023
no it should work but that said, TEXTSPLIT() was introduced even more recently so check for that also:
type =TEXT and you see a list that includes: TEXT, TEXTAFTER, TEXTBEFORE, TEXTJOIN, TEXTSPLIT, ...
that said, you can/should also go into FILE-> Account -> click Update Options -> Update Now to see/make sure you have the most up to date version.
williamdhicks
Copper Contributor
Feb 07, 2023
All three are there. Do I need to format the column in a certain way?

Forum Discussion

Resources