Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a list of names, which I am trying to separate into first, last, and middle. This works well for standard names with three parts, but is complicated where I have names with more parts. I am looking for a suggestion which will help manage this.
Source data is formatted Lastname,Firstname Middle names. That is, there is a comma (no spaces) delimiting last and first name, and spaces delimiting first name from any middle names.
Smith,Joe L
Wilson,Mike Mitchell
Lee,Johnny Lin Yang
I am using:
SubField([Full Name],',',1) as [Last Name],
SubField([Full Name],',',2) as [First Name],
SubField([Full Name],' ',2) as [Middle Name]
Which gives me
Smith Joe L
Wilson Mike Mitchell
Lee Johnny Lin Yang Lin
With longer, more complex names, my breakout doesn't work. I'm looking for:
Lee Johnny Lin Yang
What I would like to have is everything before the comma as last name (working properly), everything after the comma but before the first space as first name, and then everything after the first space, regardless of how many spaces follow, as middle name. Is there a way I can do the following?:
Add a step to the First Name function which gives me position 2 after a comma, but position 1 before a space in the same function?
Give me all the text after the first space in the Middle Name function?
If it helps, most names have 2, 3, or 4 parts as I have described here. But I have one name with 7 parts which I also want to see as First Name, Last Name, all 5 middle names combined.
I appreciate any advice!
Got confused in the last two names
1) Hayes Jr.,Michael Brian
2) Santos Melo Nogueira Dos Reis,Daniel J
Does this look right?
Hi Jason,
Test:
Load *,
SubField(Names,',',1) as [Last Name],
Subfield(Names,' ', 2) as [First Name],
Mid(Names, Index(Names,' ',2)+1) as [Middle Name];
LOAD * INLINE [
Names
"Smith, Joe L"
"Wilson, Mike Mitchell"
"Lee, Johnny Lin Yang"
];
May be this
Table:
LOAD *,
SubField([Full Name],',',1) as [Last Name],
TextBetween([Full Name],',', ' ') as [First Name],
Mid([Full Name], Index([Full Name], ' ', 1) + 1) as [Middle Name];
LOAD * INLINE [
Full Name
"Smith,Joe L"
"Wilson,Mike Mitchell"
"Lee,Johnny Lin Yang"
];
Tamil - I think you added a space after the comma, Jason specifically pointed out that there is no space
there is a comma (no spaces) delimiting last and first name
I forgot that there was no space between last name and first name (only comma). So my solution is not correct. Yours should be the correct one.
My bad, I realized my mistake after seeing your solution. Yours is correct, as usual.
this worked great. There are a couple of strange artefacts with this method (people with no middle name, people with a Jr suffix, and people with a multi-part last name). I was going to try to dig into that a little further in this thread, but I think the best thing to do is work with it the way it is.
No middle names and Jr Suffixes only impact the Middle Name field, and it is a non-issue in my function.
The one name with 7 parts to it shows up very strangely, with only the First Name being correct. But this person only uses three parts, and I can probably just make that edit in the source documents rather than try to build a function.
thanks, as always!
If you can share one example from each of the cases, we might be able to get it to work
Johnson,William:
Splits first and last correctly. Gives me Johnson,William as the middle name shown.
Hayes Jr.,Michael Brian:
First and Last are correct, including appending Jr. the last. Jr.,Michael Brian is the middle name shown.
Santos Melo Nogueira Dos Reis,Daniel J
First Name: Daniel
Last Name: Santos Melo Nogueira Dos Reis
Middle Name: Melo Nogueira Dos Reis,Daniel J
What I would want to see:
First Name: Daniel
Last Name: Reis
Middle Name: J
Alternative Middle Name: J Santos Melo Nogueira Dos (but that is a bit awkward, and Daniel J Reis would be appropriate)
Got confused in the last two names
1) Hayes Jr.,Michael Brian
2) Santos Melo Nogueira Dos Reis,Daniel J
Does this look right?