Help with SUBSTITUTE() formula

cori_parrish1 · ‎Sep 06, 2024

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!!