Can I alter the main loop to ‘orderby’ a custom callback?

No you can’t pass a callback function. Callback functions are PHP, so they can’t be run in MySQL. For that to work it would need to query the results from the database first, but you’d get the wrong results because they’re unsorted.

You would need to use the posts_orderby filter to do this as part of the SQL query:

add_filter(
    'posts_orderby',
    function( $orderby, $query ) {
        if ( ! is_admin() && $query->is_post_type_archive( 'post_type_name' ) ) {
            $orderby = "SUBSTRING_INDEX(post_title, ' ', -1) ASC, post_title ASC";
        }
        
        return $orderby;
    },
    10,
    2
);

That uses the SUBSTRING_INDEX() MySQL function to order results by everything after the first last space in the post title, and then by the post title so that results with matching surnames are sorted by first name.