Help with concatenating name field

[rcorbitt]

I am importing data that contains names in a reversed order. "Smith, John H" and "Smith, Esq., John H"

I have created a calculation field that uses the Trim and Case functions to get "John H"

Trim (Case ( PatternCount ( Reg Name 1; ","); Right ( Reg Name 1; Length (Reg Name 1) - Position (Reg Name 1; ","; 1; 1 ) ); Reg Name 1 ))

I have a second calculation field that uses the Left function to get "Smith"

Left ( Reg Name 1 ; Position ( Reg Name 1; "," ; 1 ; 1 ) - 1 )

I use a third calcuation field that puts these together. (First Name & " " & Last Name)

The problem is those records that have an additional title like "Esq." or "IV" - basically a second comma in the original data "Smith, Esq., John H", don't work. I get "John H, Esq. Smith"

Any help would be appreciated! How I accomodate the possibility of a title, or second comma, in the original text? Is it possible to have just one field with a calculation that does all of this?

I'm too much of a newbie to totally understand how calcuations work ... I've pieced these calcs together from a couple of other posts.

Thanks in advance!

[fabriceN]

I think you probably should define clearly what's in your original file, so you would be working with an extensive knwoledge of what should be handled and what shouldn't.

Then, I'm sorry to say so, but FileMaker still doesn't have any Regular Expression feature, so you will have to use turn-arounds such as custom functions like FilterValuesByTest and FilterWordsByTest (by your devoted servant, available here : http://www.bh-a.com/downloads_1.1.html#DL04 ), or a regex plug-in.

But honnestly, I would recommend the use of a better equiped tool for this conversion, such as perl or php.

[comment]

I also think that two examples are not enough to establish the rules. Try starting with something like this, which handles both, and see if there any exceptions:

Let ( [

pos1 = Position ( text ; "," ; 1 ; 1 ) ;

pos2 = Position ( text ; "," ; 1 ; 2 ) ;

len = Length ( text ) ;



last = Left ( text ; pos1 - 1 ) ;

first = Right ( text ; len - Max ( pos1 ; pos2 ) - 1 ) ;

title = Middle ( text ; pos1 + 2 ; pos2 - pos1 - 2 ) 

] ;

first & " " & last & Case ( pos2 ; " " & title )

)

Is it possible to have just one field with a calculation that does all of this?

As you can see, it's possible - but it's not recommended. Putting them together is easy, taking them apart is not (hence your current predicament). You should parse the imported into separate fields, and use a calculation field to display them together, if so required.

[Fenton]

As Fabrice says, until we see a lot of data, we can only guess. The big question is whether it has already been pre-processed to properly separate the various parts correctly in all cases, and if you can tell for sure what each is by counting the commas and looking at positions. If not then I think you're going to have some work to do.

People's names are not easy to separate accurately; it is often difficult to tell whether a "middle" word is part of the first name, a middle name, or part of the last name. About the only file I've seen that could do that properly was an old one which used lists of the possibilities (there are not all that many common ones, in English anyway) and compared to them.

Hopefully this has been done already.

A big question is whether they also handle prefixes, such as "Dr.", etc.. It seems they would. If so, how can you tell the difference between:

"Smith, Esq., John H" and "Smith, Dr., John H"?, or would it be "Smith, John H, Dr."? or what?

If the prefix is always in the last position, and only suffixes and prefixes have periods, you can tell the difference. But if they put the suffix or prefix in the same position, it's going to be tough. You'd have to compare to a list of all possibilities for each.