Forum Discussion
Solved
Sorting Within a Single Cell
I am trying to sort text information within a single cell so that the latest entry is at the top as opposed to the bottom. The manual effort would be very time consuming (see example). BEFORE ...
NanaNina note that Patrick2788 works unless you cross over the year because it is text sorting those dates. Here is an option to actually use the date VALUES:
=LET(in, A1, lines, TEXTSPLIT(in,,CHAR(10)), sorted, SORTBY(lines,DATEVALUE(TEXTBEFORE(lines,":")),-1), TEXTJOIN(CHAR(10),,sorted) )
With a formula you could use:
=LET(split,SORT(TEXTSPLIT(A1,,CHAR(10)),,-1),TEXTJOIN(CHAR(10),,split))
Then enable wrapping on the cell or column where the formula is used.
NanaNina note that Patrick2788 works unless you cross over the year because it is text sorting those dates. Here is an option to actually use the date VALUES:
=LET(in, A1, lines, TEXTSPLIT(in,,CHAR(10)), sorted, SORTBY(lines,DATEVALUE(TEXTBEFORE(lines,":")),-1), TEXTJOIN(CHAR(10),,sorted) )- Good Day mtarler. Something quite odd has occurred. The formula you provided doesn't appear to work anymore. I know, that makes no sense. But when I try the formula against data in another spreadsheet, it returns the same result as if it doesn't recognize the formula. The cell is formatted exactly like the cell in the other spreadsheet where it does work. Any ideas?
- Hard to say without seeing it but I would double check line 1 where it says:
=LET(in, A1,
and make sure it isn't saying something like
=LET(in, 'OtherWorkbook'Sheet1!A1,
- You were absolutely correct!!! I was going through each cell (after doing a paste special) and manually moving the dates that crossed into the previous year. You saved the day!! Thanks much and thanks Patrick2788 for your contributions as well!
- Eureka!!! That worked!!! YAAASSS!!! Thanks so much!
- Glad it worked!
