Help

Formula to extract last 3 letters of author's last name with prefixes and suffixes

Topic Labels: Formulas
Solved
Jump to Solution
931 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Ashley_Jackson
7 - App Architect
7 - App Architect

Hey guys,

I've got a base that tracks books in a small library.  I need to create a field that extracts the last 3 digits of the author's last name and it has become very complicated, haha.

Example author names:

  • John Bunyan
  • C.S. Lewis
  • Alister E. McGrath
  • C. Everett Koop
  • Dr. William Lindner Jr.

Authors very often have prefixes like Dr. or Rev. and they like including initials and suffixes, too - lol!

So how do I build a formula that will pull the last 3 letters of the last name like this:

  • John Bunyan = BUN
  • C.S. Lewis = LEW
  • Alister E. McGrath = MCG
  • C. Everett Koop = KOO
  • Dr. William Lindner Jr. = LIN

This gets me very close:

 

 

 

UPPER(LEFT(REPLACE({Author(s)},1,FIND("~",SUBSTITUTE({Author(s)}," ","~",LEN({Author(s)}) - LEN(SUBSTITUTE({Author(s)}," ","")))),""), 3))

 

 

...but it breaks with suffixes 😞

Can anyone help me tweak it to work with suffixes???

Thanks so much for any help!

1 Solution

Accepted Solutions
Sho
11 - Venus
11 - Venus

Hi ,
I tried this in Regex.
It removes one or two characters and the period, and extracts the first 3 characters of the last name.

 

UPPER(REGEX_EXTRACT(REGEX_REPLACE(Name, "\\w{1,2}\\.",""),".*\\s(\\w{3}).*$"))

 

 

 

See Solution in Thread

3 Replies 3
Sho
11 - Venus
11 - Venus

Hi ,
I tried this in Regex.
It removes one or two characters and the period, and extracts the first 3 characters of the last name.

 

UPPER(REGEX_EXTRACT(REGEX_REPLACE(Name, "\\w{1,2}\\.",""),".*\\s(\\w{3}).*$"))

 

 

 

you mean 'first 3 letters'?
at first, try to get rid of end suffixes, like

IF(RIGHT(Field,2)='r.', LEFT(Field,len(Field)-4, Field)

it works with any with r. ending.
Then use regex to get last word (btw, ask chatGPT to create regex, it might provide many helpful info amd sometimes even create. Say 'him'  - it's for Airtable, so it suggest the correct format.
smth like '\s(\w+)$'     - a space(\s), some letters(\w), end of string($)
then wrap it all in LEFT( ........   , 3)

This worked perfectly, thank you!!!