Help with SUBSTITUTE() formula

[cori_parrish1]

i'm trying to make a formula to pull out the last name from a field that sometimes has full names in it & sometimes does not. i found this formula in a very helpful Airtable blog:
TRIM(MID(SUBSTITUTE(Employer," ",REPT(" ",LEN(Employer))), (3-1)*LEN(Employer)+1, LEN(Employer)))this worked for me when i was parsing a field that always had "Title First Last" as the number of words. however, my new field sometimes has a middle initial. i changed the formula to (4-1) to account for the middle initial, but it doesn't work for names without a middle initial.
i'm guessing there's a way to tell the formula to look for the word that is the right-most word separated by a space, but honestly i don't even fully understand this formula, so i'm not sure how to modify it.Here's an example of data in the original field & the output:i'm using an IF() function to determine if the field has names and titles in it or not, do i need to do a nested if based on whether a middle initial exists?also, i know that Airtable has UPPER() and LOWER() text functions, can it also convert to title case (so that the first letter of the word is capitalized and the rest are lowercase), or do i need to run my spreadsheet through Excel before importing into Airtable for that issue?thank you so much for any assistance you can provide!!

[cori_parrish1]

found an answer about the title case, which is that Airtable doesn't do that currently, and that this helpful tool can adjust the data: https://community.airtable.com/t5/show-tell/field-tweaker-script-block-for-in-place-transformations/...

still have the other question about the middle initial. thanks!

[TheTimeSavingCo · Accepted solution]

Hmm, does this look right? 

REGEX_EXTRACT(Name, "[^ ]+$")

[cori_parrish1]

omg, thank you thank you!!