Using RegEx tool for finding FirstName, MiddleName and LastName from a Name field
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Labels:
- Transformation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Try this, works for me without issues:
^(\<\w+\>)\s(.+)\s(\<.+\>)$
