Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Help with SUBSTITUTE() formula

Topic Labels: Formulas
Solved
Jump to Solution
302 3
cancel
Showing results for 
Search instead for 
Did you mean: 
cori_parrish1
6 - Interface Innovator
6 - Interface Innovator

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:Screenshot 2024-09-06 120820.pngi'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!!

1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

Hmm, does this look right? 

Screenshot 2024-09-07 at 10.28.07 AM.png

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

See Solution in Thread

3 Replies 3
cori_parrish1
6 - Interface Innovator
6 - Interface Innovator

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
18 - Pluto
18 - Pluto

Hmm, does this look right? 

Screenshot 2024-09-07 at 10.28.07 AM.png

REGEX_EXTRACT(Name, "[^ ]+$")
cori_parrish1
6 - Interface Innovator
6 - Interface Innovator

omg, thank you thank you!!