How to sort 3 unusual type of dates in Excel?

Question

I have 3 types of dates in my excel files. These dates include the above : Q (quarter), Apr (Abbreviation or abbreviated name of the month), April (Full name of the month). These are combined with the four-digit year number and show part of the year as a date in my Excel files. Now i want to sort this 3 type of dates in my excel files.
For Example:

I need to first shift all the four digit year numbers to the left and then sort by quarter or full name or short name of the month from lower to upper.

I put this example here:

Q1 2022
2000 November
2024 Q3
Apr 2010
Q2 2022
2015 Jan
March 2005
2024 Q4
May 2010
2015 Feb
2000 October
April 2005

Note: 1) There are always these three types of year divisions in my files 2) I can't do this manually because I might have too many files. Or have many partitions in the column
How can i do this?

Answer 1

For the case where you really need to swap two words in a string, depending on where the number is, the user-defined function (UDF) code is very simple:

Function OrderWords(sWord As String) As String
Dim aTemp As Variant
    OrderWords = Trim(sWord)
    aTemp = Split(sWord, " ")
    If UBound(aTemp) = 1 Then
        If IsNumeric(aTemp(1)) Then OrderWords = aTemp(1) & " " & aTemp(0)
    End If
End Function

If you want to get the date range for each of the rows, then the function will be a bit longer:

Function ParsePeriods(sWord As String) As Variant
Dim aTemp As Variant
Dim nYear As Integer
Dim sPeriod As String
Dim aRes(1 To 2) As Variant
    ParsePeriods = OrderWords(sWord)
    aTemp = Split(Trim(sWord), " ")
    If UBound(aTemp) <> 1 Then Exit Function
    If IsNumeric(aTemp(1)) Then
        nYear = CInt(aTemp(1))
        sPeriod = Left(Trim(aTemp(0)), 3)
    Else
        nYear = CInt(aTemp(0))
        sPeriod = Left(Trim(aTemp(1)), 3)
    End If
    Select Case sPeriod
        Case "Q1"
            aRes(1) = DateSerial(nYear, 1, 1): aRes(2) = DateSerial(nYear, 3, 31)
        Case "Q2"
            aRes(1) = DateSerial(nYear, 4, 1): aRes(2) = DateSerial(nYear, 6, 30)
        Case "Q3"
            aRes(1) = DateSerial(nYear, 7, 1): aRes(2) = DateSerial(nYear, 9, 30)
        Case "Q4"
            aRes(1) = DateSerial(nYear, 10, 1): aRes(2) = DateSerial(nYear, 12, 31)
        Case "Jan"
            aRes(1) = DateSerial(nYear, 1, 1): aRes(2) = DateSerial(nYear, 1, 31)
        Case "Feb"
            aRes(1) = DateSerial(nYear, 2, 1): aRes(2) = Application.WorksheetFunction.EoMonth(aRes(1), 0)
        Case "Mar"
            aRes(1) = DateSerial(nYear, 3, 1): aRes(2) = DateSerial(nYear, 3, 31)
        Case Else
            aRes(1) = "Write the rest of": aRes(2) = "the options yourself"
    End Select
    ParsePeriods = aRes
End Function

Since the function returns two values, you need to enter it in two adjacent cells as an array formula, with pressing Ctrl+Shift+Enter

The second option is preferable because with the first option you won't get the result shown in your screenshot - when sorting 2005 March will be lower than 2005 April because "A" is less than "M".