I need to process a tab-delimited file. The source is "Adams, John Q". I need to parse it into two fields: nameLast and nameFirst. (I don't need the middle initial.) nameLast is a straightforward LeftWords calculation. But I'm stuck on how to ignore the comma and and the middle initial to extract the first name. Can anyone help?

FMPA 18
macOS 10.15.6
 

I think we need to know more about how your data is organized. Can a person have more than one first name (e.g "Jean-Claude" or "Ann Marie")? Does every person have a middle initial? Is the middle initial always a single letter?

6 hours ago, -dp- said:

nameLast is a straightforward LeftWords calculation.

Only if you can be sure that the last name is a single word - which is very often not the case. I would extract the last name as =

Left ( FullName ; Position ( FullName ; "," ; 1 ; 1 ) - 1 )

and then the rest of the text as =

Right ( FullName ; Length ( FullName ) - Position ( FullName ; ", " ; 1 ; 1 ) - 1 )

or, if you prefer =

Replace ( FullName ; 1 ; Position ( FullName ; ", " ; 1 ; 1 ) + 1 ; "" )

 

Edited August 18, 20205 yr by comment

Thank you. This is very helpful, even though I don't fully understand it yet. The last name function returns the last name. Perfect. Both of the other functions return both the first name and middle initial. So, for "Doe, John Q" it returns "John Q". How can I get it to return simply "John"?

21 minutes ago, -dp- said:

Both of the other functions return both the first name and middle initial.

Yes, I said it returns the rest of the text. I don't know how to reliably separate the first name from the initial based on a single example. That's why I asked what are the rules by which your input is organized.  

 

37 minutes ago, -dp- said:

So, for "Doe, John Q" it returns "John Q". How can I get it to return simply "John"?

It can be helpful to export the name field and attach that file here.  The reason it isn't as clean and easy is because your data can look like:

Mary Jo L Simpson

Mary L Smith-Cunningham

... etc

ps how in heck to get a single line carriage return here?

Edited August 18, 20205 yr by LaRetta

Ah, I see. Aside from what can be discerned visually, I don't know the rules of organization. A system over which I have no control generates a screen-only report, which I highlight, copy, and paste into a text editor. Then I'll import it into FileMaker for further processing. What I'm attempting is sync between two systems that have no intention of communicating with each other. The source does contain hyphenated last names and double-barreled first names, so your calculations have been enormously helpful. I can't rule out double middle initials, but a visual scan doesn't spot any. Thanks.

I'm reluctant to share the file, even just the names. I'm a school. These are minors. But the file structure is very simple. Each line begins with "lastName, firstName MI" (without "") followed by a tab and a bunch of attendance codes.

Well, if you could be sure there will always be exactly one middle initial, then you could do simply:

Let ( [
tail = Right ( FullName ; Length ( FullName ) - Position ( FullName ; ", " ; 1 ; 1 ) - 1 )
] ;
LeftWords ( tail ; WordCount ( tail ) - 1 )
)

Otherwise you will need to check if the last word is an initial - and if there can be more than one, perhaps continue to test the word before last and the one before that, in a loop, until the test returns false. Then comes the question how to determine if a word is an initial: you could test for length of 1 - but then you will trip over "CJ". Perhaps you could test for capital characters only. The point is it's very difficult to make up rules for parsing data without knowing the rules by which the data was put together.

Is it even important to separate the first names/s from the initial/s? Your data provider didn't think so.

 

1 hour ago, LaRetta said:

how in heck to get a single line carriage return here?

Shift-return works for me.

 

Edited August 18, 20205 yr by comment

Ah. I had forgotten.  CTRL-return submitted my post!  Thanks, Michael!

This works perfectly. Thank you very much. I'm trying to wrap my mind around the calculation and make this a learning experience. Here's my best shot.

- Search FullName for the comma.
- Set the target length to FullName minus the comma.
- Select the right content of FullName
- Put all of the above into a variable named "tail".
- Select the left words in tail, with the number of words set to tail minus 1.

The last part doesn't make sense to me. Would you mind elaborating?

I commend you for your curious spirit and desire to learn. 🙂

If you assume that the last (rightmost) word is the initial, then you want all the words (starting from left) except the last one. Hence:

LeftWords ( tail ; WordCount ( tail ) - 1 )

If the tail contains "John Q" (2 words) then this will return the first word.
If the tail contains "Jean-Claude Camille F" (4 words) then this will return the first 4 - 1 = 3 words.

 

3 minutes ago, LaRetta said:

I commend you for your curious spirit and desire to learn. 🙂

Indeed. We haven't had that spirit here since 1969. 😁

 

Well, we keep telling the students that they need to be life-long learners.... : )

Got it! The light just came on. Thank you.