Skip to main content

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!

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)

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}).*$"))

 

 

 


This worked perfectly, thank you!!!

Reply


Terms & Conditions