Using RegEx tool for finding FirstName, MiddleName and LastName from a Name field

[GauravRawal]

Hi,

I am trying to fetch the FirstName, MiddleName and LastName from a Name field using RegEx tool. I created this below expression for the same:

(^\<\w+\>)*(\s\<\w+\>)*(\s\<\w+\>)$

For the following data:

I am facing two issues while reading this data,

a. For some reason, the first name of the first row is missing and coming as blank.

b. If the middle name contains two words like the 2nd row, the 2nd word is being picked up as the middle name and not the whole NameM1 NameM2.

The results are shown below:

Could someone suggest the changes in the RegEx expression?

Thanks.

[MarqueeCrew]

Hi @GauravRawal,

Using the RegEx Library as a source....

http://regexlib.com/UserPatterns.aspx?authorId=c678bc8b-ac0f-4472-93b1-40178cd4e0a4

I found this:

Regex_replace([Name],"(?<FirstName>[A-Z]\.?\w*\-?[A-Z]?\w*)\s?(?<MiddleName>[A-Z]\w+|[A-Z]?\.?)\s(?<LastName>(?:[A-Z]\w{1,3}|St\.\s)?[A-Z]\w+\-?[A-Z]?\w*)(?:,\s|)(?<Suffix>Jr\.|Sr\.|IV|III|II|)",'$1|$2|$3|$4')

It functions as:

Description	Getting closer now... this one has the functionality of the previous two and matches on names like "Jill St. John" as well.
Matches	David F. Walker | Norm MacDonald | Jill St. John
Non-Matches	oscar peterson

So you might need another expression for First/Last only.

But this handles names that you haven't considered yet.

Cheers,

Mark

[Inactive User · Accepted solution]

Try this, works for me without issues:

^(\<\w+\>)\s(.+)\s(\<.+\>)$