Forum Discussion

NanaNina's avatar
NanaNina
Copper Contributor
May 15, 2023
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

AFTER

 

excel
  • mtarler's avatar
    mtarler
    May 17, 2023

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

     

12 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    May 17, 2023

    NanaNina 

    This requires 365 but then, there are currently no other versions of Excel that I would consider using.

    = LET(
      dates, DATEVALUE(TEXTBEFORE(information, ":")),
      SORTBY(information, dates, -1)
  )
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    May 17, 2023

    NanaNina 

    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.

    • mtarler's avatar
      mtarler
      Silver Contributor
      May 17, 2023

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

       

      • NanaNina's avatar
        NanaNina
        Copper Contributor
        Jun 06, 2023
        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?
    • NanaNina's avatar
      NanaNina
      Copper Contributor
      May 17, 2023
      Eureka!!! That worked!!! YAAASSS!!! Thanks so much!
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 15, 2023

    NanaNina 

    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 Function

    With 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.

    • NanaNina's avatar
      NanaNina
      Copper Contributor
      May 17, 2023
      Good Day. I am receiving a compile error: Expected: end of statement.
      Any ideas?

Resources