Sorting by numbers when a cell contains text

[NRF95]

Hello! Please, take a look at the image that i uploaded. My goal is to sort the numbers 2021/1, 2021,2 and so on. The problem is that right now they are sorted like 2021/1, 2021/10. I want the sorting to keep in mind that 2022 is larger than 2021, but at the same time to keep in mind thar 10, 11, 12 are larger than 2. What formulas should i use to achieve proper sorting? Thanks in advance.

 

[Fluff]

Hi & welcome to MrExcel.
You could put this in column B & sort on that

=IF(LEN(A4)=6,REPLACE(A4,6,0,0),A4)

 

[NRF95]

Hi & welcome to MrExcel.
You could put this in column B & sort on that

=IF(LEN(A4)=6,REPLACE(A4,6,0,0),A4)

It worked! Thank you so much, sir! Can you please elaborate on what you did with this if nesting, so that i can use it on my own in similar cases as well in the future?

 

[Fluff]

It checks if the cell has 6 characters & if so it adds a 0 after the /

 

[Flaiban]

Try this code , NRF95...

Public Sub NRF95()
    Const START_ROW = 2, START_COL = 1
    Dim ws As Worksheet, lr As Long, lFormula As String, rFormula As String
    Dim sortL As Range, sortR As Range

    Application.ScreenUpdating = False
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lr = ws.Cells(Rows.Count, "A").End(xlUp).Row

    ws.Columns(START_COL + 1).Insert Shift:=xlToRight
    ws.Columns(START_COL + 2).Insert Shift:=xlToRight
    lFormula = "=LEFT(" & Replace(ws.Cells(START_ROW, START_COL).Address, "$", "") & ",5)"
    rFormula = "=RIGHT(" & Replace(ws.Cells(START_ROW, START_COL).Address, "$", "") & ",2)"

    With ws.UsedRange   'Apply Formulas
        .Columns(START_COL + 1).Offset(1).Formula = lFormula
        .Columns(START_COL + 2).Offset(1).Formula = rFormula
        Set sortL = .Columns(START_COL + 1).Offset(1).Resize(lr - 1)
        Set sortR = .Columns(START_COL + 2).Offset(2).Resize(lr - 1)
    End With

    With ws.Sort        'Apply Sort
        With .SortFields
            .Clear
            .Add Key:=sortR
            .Add Key:=sortL
        End With
        .SetRange ws.UsedRange.Offset(1).Resize(lr - 1)
        .Apply
    End With

    ws.Columns(START_COL + 2).Delete    'Remove helper columns (if needed)
    ws.Columns(START_COL + 1).Delete    'Remove helper columns (if needed)
    Application.ScreenUpdating = True
End Sub