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.
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)) )- The first didn't work and the second had too many / few parenthesis but I couldn't get it to work either.
- BOTH of those version should work if you have 365. Did you open the attached file or copy and paste? IF you copy and paste you MUST paste it directly into the formula box at the top (or hit F2), if you click on a cell and just hit paste on the sheet itself, it will paste onto multiple cells and the formula will be broken.
