Skip to main content
Solved

Formula to Extract Last Name from Full Name

  • January 18, 2023
  • 8 replies
  • 305 views
Cole_Wedemeier
Forum|alt.badge.img+13

Hello!

I know at one time there were several threads that spoke to extracting the First Name from a full name, and also a formula for extracting the Last Name. While I can find several threads with the code for First Name, I cannot find any that show for Last Name now that I'm in need of it 😂

First Name Formula Found: 

IF(FIND(" ",{Full Name})=0, {Full Name}, LEFT({Full Name}, FIND(" ",{Full Name})-1))

I've tried different variations using RIGHT instead of left, but I either get really odd returns where some last names are extracted properly, and others are completely way off, or I get blank returns. 

I have several Full Names that include more than 1 name as that Last Name. Example: Olivia Gutierrez Lawson. I would need "Gutierrez Lawson" to appear in the last name field with the formula. 

I feel confident in some of my formula creation skills and then there are times like this when I realize just how little I know haha. I am so grateful there is a community of people who are way better at understanding formulas than I am that I can ask for help from.

Best answer by Steve_Haysom

try this: 

First name: REGEX_EXTRACT(Name, "^[^ ]+")
 
Last name: REPLACE(Name, 1, LEN({First name}) + 1, "")

 

J

8 replies

Ron_Daniel
Forum|alt.badge.img+21
  • Ron_Daniel
  • Inspiring
  • January 18, 2023

One of the issues you'll run into with extraction is that there is no way to differentiate whether a space is in the first name ("Mary Ann") or the last name ("Gutierrez Lawson"). Is it possible to use separate fields for first and last names and then concatenate them when you need the full name?

Cole_Wedemeier
Forum|alt.badge.img+13
  • Cole_Wedemeier
  • Author
  • Inspiring
  • January 18, 2023

One of the issues you'll run into with extraction is that there is no way to differentiate whether a space is in the first name ("Mary Ann") or the last name ("Gutierrez Lawson"). Is it possible to use separate fields for first and last names and then concatenate them when you need the full name?


@Ron_Daniel that is the typical way I setup every table, however, someone set this one up and I'm trying to make it work without having to do a ton of manual work now afterwards 🙃

I'm willing to risk the first name being split up as I recognized that as a potential issue too. 

Ron_Daniel
Forum|alt.badge.img+21
  • Ron_Daniel
  • Inspiring
  • January 18, 2023

Ah, inheritance!
Well, it's less than ideal, but you can make it work (mostly) like this:

First Name formula is

IF(FIND(" ",{Full Name})=0, {Full Name}, LEFT({Full Name}, FIND(" ",{Full Name})-1)) 
Last Name formula is
REPLACE({Full Name},1,FIND("~",SUBSTITUTE({Full Name}," ","~",LEN({Full Name}) - LEN(SUBSTITUTE({Full Name}," ","")))),"")
Lisa_Wetherby
S
Forum|alt.badge.img+11
  • Steve_Haysom
  • Inspiring
  • Answer
  • January 19, 2023

try this: 

First name: REGEX_EXTRACT(Name, "^[^ ]+")
 
Last name: REPLACE(Name, 1, LEN({First name}) + 1, "")

 

Ron_Daniel
Cole_Wedemeier
KenD
Ron_Daniel
Forum|alt.badge.img+21
  • Ron_Daniel
  • Inspiring
  • January 19, 2023

@Steve_Haysom 's solution is shorter, more elegant, and produces better results. Win!

S
Forum|alt.badge.img+11
  • Steve_Haysom
  • Inspiring
  • January 19, 2023

@Steve_Haysom 's solution is shorter, more elegant, and produces better results. Win!


thanks Ron! :@)

Cole_Wedemeier
Forum|alt.badge.img+13
  • Cole_Wedemeier
  • Author
  • Inspiring
  • January 20, 2023

try this: 

First name: REGEX_EXTRACT(Name, "^[^ ]+")
 
Last name: REPLACE(Name, 1, LEN({First name}) + 1, "")

 


@Steve_Haysom THANK YOU! That works like a dream and I would have NEVER come up with that myself. 

Much appreciate to you as well, @Ron_Daniel!

Ron_Daniel
Ron_Daniel
Forum|alt.badge.img+21
  • Ron_Daniel
  • Inspiring
  • January 20, 2023

@Cole_Wedemeier  - this has prompted me to finally focus on learning regular expressions (REGEX). They're super-powerful for all of us to have in our formula toolbox!

Terms & ConditionsAccessibility statement