Sorting the results of a formula alphabetically

[liampog]

Hi there

In Cells A2:A1001 there are customer full names - first and last name and any middle names (example: John James Smith)

In Cells B2:B1001 there are the membership numbers of the customers

In Cells C2:C1001 I have the following equations which extract the last name of the customers so I can sort by last name:

=IF(A2<>"",RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))),"")

I have a Worksheet_Change event that monitors when the membership number is entered and then sorts all the names by the surname (column C).

However, it is sorting all the names in alphabetical order but moving them to the bottom of the range ending at Row 1001.

I think it's because the sort command is sorting the actual formulas rather than the results of the formulas.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Application.ScreenUpdating = False
    
    If Not Intersect(Target, Columns("B")) Is Nothing Then
        ActiveSheet.Unprotect
        Range("A2:C1001").Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        ActiveSheet.Protect
    End If

    Application.ScreenUpdating = True

End Sub

Is there any way to fix the sorting code so that it sorts the actual results of the formulas rather than the formulas themselves.

I have my reasons for having the customer's full name in one cell rather than splitting them into columns for First, Middle and Last Names.... that being that I have the name column set up as a named range which I then refer to from another sheet to create a dropdown Data Validation selection cell.

Thanks in advance,
Liam