Order category posts by last word of custom field

I’m trying to order a category page by the last word of a custom field that all of its posts have. The custom field represents a name, so the last word would be the last name. In the end, the posts of the category page should be listed in alpha order by last name.

How do I achieve this?

Threads I’m looking at:

• How to order posts by meta value?, which covers everything except how to order by the last word of the meta key.
• Order by the results of a function with WP_query, but I’m not using a WP_Query since I’m just changing the order of a category.php page (so, correct me if I’m wrong, but WP_Query shouldn’t be necessary in that case, correct?).

My code:

function sort_research_cycles_by_last_name($query) {
    $isResearchCategory = current_cat_is_sub_of(get_category_by_slug('research-cycles')->term_id) || current_cat_is_sub_of(get_category_by_slug('profile-expert-fr')->term_id);
    if ($isResearchCategory && $query->is_main_query()) {
        /*
         * Uncommenting the following two lines and setting orderby to $orderby
         * results in an odd order that I can't make sense of.
         */
        // global $wpdb;
        // $orderby = "SUBSTRING_INDEX($wpdb->postmeta.meta_key, ' ', -1)";
        
        /*
         * This currently only orders posts by alpha order of the first word
         */
        $query->set('meta_key','wpcf-name-of-researcher');
        $query->set('orderby','meta_value');
        $query->set('order','ASC');
    }
}
add_action('pre_get_posts','sort_research_cycles_by_last_name');