Sort Custom Post Type by Second Word in Title

You need a little more code to make sure it happens only when you want it to.
If this query were the only place that these posts are listed, you could add it right after the $args assingment.
add_filter( 'posts_orderby', 'posts_orderby_lastname' );
and remove it at the end
remove_filter( 'posts_orderby', 'posts_orderby_lastname' );

But because it is a CPT, there are other places these posts can be listed, such as the post-type archive page. (If your code is for that page, you don’t need to do your own query.)
If you want them ordered this way regardless of where they are queried, you would put an if statement into the filter to see if it’s a post-type archive, and only make your change if it is. Then you can add_filter somewhere global, in your functions.php file.

Thanks for your reply! This is not the CPT archive page, it’s a custom page template where I’m displaying posts from the “njapf” taxonomy term within the “People” CPT.

I’m ok if all other instances of the “People” CPT are sorted by last name (second word of title).

Given that, which method might be best for implementing the filter?

Well, the way you word it
“I’m ok if all other instances of the People CPT are sorted by last name”
makes it sound like you didn’t quite understand.
If you want to affect just this query, put add_filter right before the query and then remove_filter right after.
If you put the filter anywhere else, you will need to put an if statement in the filter to make sure it only affects your CPT and not Posts and Pages and Attachments, etc.

I just want the filter to affect this query.

Here’s how I’ve added the filter to my query:

	<?php 

$args = array(
'post_type' => 'people',
'orderby'   => 'title',
'order' => 'ASC',
'tax_query' => array(
array(
'taxonomy' => 'people',
'field' => 'slug',
'terms' => 'njapf'
)
)
);

add_filter( 'posts_orderby', 'posts_orderby_lastname' );
//$query = new WP_Query( $args ); // this line is useless in your code
 
// The Query
$the_query = new WP_Query( $args );

 
// The Loop
if ( $the_query->have_posts() ) {
while ( $the_query->have_posts() ) {
$the_query->the_post();

echo '<div class="artist-grid-item">';

if(has_post_thumbnail()){

	echo '<a href="'. get_permalink() . '">';

               echo '<div class="artist-grid-image">' . get_the_post_thumbnail( $_post->ID, 'large' ) . '</div>';
             }

echo '<p>' . get_the_title() . '</p>';

echo ' </a></div>';

}

} else {
// no posts found
}

remove_filter( 'posts_orderby', 'posts_orderby_lastname' );

/* Restore original Post Data */
wp_reset_postdata();?>

It re-ordered the posts, so something happened, but not what was expected. They’re not ordered by last name, but appear to be in some sort of random order.

Again, here’s my filter that I added to functions.php:

function posts_orderby_lastname ($orderby_statement) 
{
  $orderby_statement = "RIGHT(post_title, LOCATE(' ', REVERSE(post_title)) - 1) DESC";
    return $orderby_statement;
}

I wonder if there’s an issue with that instead.

Well, it does assume that the last name is last. What it does is innermost first, so reverse the name, find the space, subtract one giving the length without the first name, use that many characters from the right of the original, and do it descending.
As example of “First Last”
tsaL tsriF
space is at 5
use right 4: “Last”
But example of “First Last Jr.”
.rJ tsaL tsriF
space is at 4
use right 3: “Jr.”

You might not want it Descending (Z to A).
If nothing else changed the filter, it should be doing what you want.