Skip to main content
Solved

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

  • June 22, 2023
  • 3 replies
  • 53 views
Ashley_Jackson
Forum|alt.badge.img+17

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!

Best answer by Sho

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

 

 

 

3 replies

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • Answer
  • June 23, 2023

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

 

 

 

Alexey_Gusev
Forum|alt.badge.img+25

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)

Ashley_Jackson
Forum|alt.badge.img+17

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

Terms & ConditionsAccessibility statement