LAST NAME, FIRST NAME

[MacFileman]

First, I am sorry that this is most definitely a repeated topic and probably pretty common, however, I could not find it in this forum or on the internet and had to post the question.

I am stuck with a mailing list that has 13,000 names set up with "Last Name, First Name," which obviously needs to become First Name (Space) Last name with no comma.

This name field contains all of the headaches associated with people's names. Examples as follows:

Smith, Bob

Smith Jr., Bob

Smith-Wesson Jr., Bob

Smith Wesson IV, Bob Peter

 

The good news, is that the list is solid with the respect that the there is only ONE comma. I need to simply reverse this order and delete the comma.

 

Thank you in advance! This is a wonderful community and I wracked my brains on this now for too long and need your help!

 

Mike

 

 

 

Edited November 20, 2018 by MacFileman
Name Edits

[comment]

6 minutes ago, MacFileman said:

The good news, is that the list is solid with the respect that the there is only ONE comma.

That is indeed good news. I would suggest you extract the parts into two separate fields. Then you will be able to easily recombine them in any way you wish.

Use =

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

to extract the last name, and =

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

to extract the first.

 

[MacFileman]

Thanks for the fast response....

 

Ok... the left works well! The right is not working. It does not recognize "LENGTH" and I get an error in my calculation. Not sure what value to place there.

[comment]

9 minutes ago, MacFileman said:

It does not recognize "LENGTH"

Can we see a screenshot of that?

[MacFileman]

Give me 5 minutes... will post my file. Due to sensitivity, need to remove actual address and I will shorten the list. : )

[comment]

Your file will not show your mistake (since it won't let you close the calc window). A screen shot will.

[MacFileman]

So the left works perfect... right is now "Can not find field?"

I changed all "Full name" to "supplied name"

SMH.... I quit filemaker and restarted. YOUR CALCULATION WORKS!

 

 

OMG THANKS!!!!

THIS DOES WORK PERFECTLY!! No idea why it didn't;t work the first time. But I quit the app... walked away, came back and it worked.

 

[comment]

There should be a highlight on the problematic part. Without seeing that, it's hard to say. You could have picked a gremlin character when copying here. Try typing it in manually. Or go through a text editor that can show invisible characters.

[MacFileman]

I can not thank you enough!!

While I am here though, I am trying to understand the code. I do not do this everyday like you might, but I have done some powerful programs with filmmaker. 

Mostly, I am trying to understand what the 

; 1 ; 1 ) - 1 )

What that means in this calculation at the end here.

[comment]

You have to parse this like you would a math expression - from the parentheses outward. First you find the position of the comma:

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

Then you subtract that from the overall length of the full name to get the count of characters on the right side of the comma. And you have to subtract 1 to account for the space after the comma.

 

Edited November 21, 2018 by comment

[MacFileman]

WOW! You just made that make sense to me!

I can't thank you enough!

[Chuck]

Just an alternative that I've used in similar situations, which I find more readable and understandable:

First Name:

GetValue ( Substitute ( SUPPLIED NAME ; ", " ; "¶" ) ; 2 )

Last Name:

GetValue ( Substitute ( SUPPLIED NAME ; ", " ; "¶" ) ; 1 )

Converting text delimited with a specific string to a list often makes parsing it much easier.