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...
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))
)
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?
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?
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)) )- Yes, it will likely always be number space text (12 ft), or number dash fraction text (2-1/2 in).
- 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?
- 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. - 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 - 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. - "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.