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) )
Here is a custom VBA function that should work in all (desktop) versions of Excel:
Function SortLines(s As String) As String
Dim arrLines() As String
Dim i As Long
Dim j As Long
Dim t As String
Dim di As Date
Dim dj As Date
If s = "" Then
SortLines = ""
Else
arrLines = Split(s, vbLf)
For i = 0 To UBound(arrLines) - 1
For j = i + 1 To UBound(arrLines)
di = DateSerial(Mid(arrLines(i), 7, 2), Mid(arrLines(i), 1, 2), Mid(arrLines(i), 4, 2))
dj = DateSerial(Mid(arrLines(j), 7, 2), Mid(arrLines(j), 1, 2), Mid(arrLines(j), 4, 2))
If di < dj Then
t = arrLines(i)
arrLines(i) = arrLines(j)
arrLines(j) = t
End If
Next j
Next i
SortLines = Join(arrLines, vbLf)
End If
End FunctionWith data as in your example in A2, enter the following formula in B2:
=SortLines(A2)
This can be filled down.
If required, you can copy a column with such formulas and paste it as values over the original range.
In Microsoft 365, a formula-only solution would be possible.
Good Day. I am receiving a compile error: Expected: end of statement.
Any ideas?
Any ideas?
"Expected: End of Statement" suggests that you didn't copy/paste the code correctly.
- Hmmm, I copied it right from the message. I will play with it when I have time. The solution from Patrick2788 worked for me. 🙂 Thanks for your help though.