Skip to main content

My primary field is an employee’s full name in this format: Last, First. The catch is that some employees have a middle initial or a middle name, so it looks like this: Last, First Middle.

I want to create two separate columns that separately extract the last name and the first name. I want the separate first name column to include the middle name/initial (if they have one).

I’ve been able to create the last name field with this formula: LEFT({Person Name}, (FIND(",", {Person Name}, 1)-1))

However, when I use that same formula for the first name field except with the RIGHT function it doesn’t pull out the first name correctly for everyone. RIGHT({Person Name}, (FIND(",", {Person Name}, 1)-1))

Any ideas?

Solved it: SUBSTITUTE( {Person Name}, LEFT( {Person Name}, FIND( ",", {Person Name} ) + 1 ), "" )

Hi,
You can use REGEX for it
 

REGEX_EXTRACT( Fieldname ,'\\w+, (\\w+) ?')

Means 'some(one or more) letters, comma, space, some letters, space(maybe)'
brackets means which part to extract. So here First name extracted, and if you take first ‘some letters’ in brackets, '(\\w+), \\w+ ?' it will extract Last Name.
And if you have some names without comma, add question mark after it. So it will be comma(maybe).

You can copy it to ChatGPT and ask to explain how this REGEX works. 
Or, next time you need build something about ‘extract part of string’, you can try to use ChatGPT to build REGEX_EXTRACT formula, by just explaining what you want, like here in comment. Much better if you provide 1-2 examples.
 

Reply


Terms & Conditions