Changing order of text in field

[deego55]

Hi-

I have a text field with several author's names. They are listed as follows: "Gene F. Franklin, J. David Powell, Abbas Emami-Naeini". Is it possible to create a calculation so that they will reformat like this (last name first): "Franklin, Gene F." [color:"red"] and place each authors name in their own field ? This is beyond my know-how! Thanks for any help!

Dave

[LaRetta]

Hi Dave,

Split the names into FirstName, MiddleName and LastName also. And, although you can split this data, the odds are high that not all will convert correctly. For instance, you can use RightWords(NameField; 1) to get the last name but what if the name is Gene F. Franklin Jr. And the issue can get quite complex allowing for Mr. or Mrs. at the beginning, hyphenated names, etc. Here is what I would suggest:

Create calculations first (for FirstName, MiddleName and LastName) and display the results next to your NameField. As you review the NameField and resulting calculations you will spot things and can begin tweaking your calculations to catch most every condition you encounter. Many here on Forums can help you fine-tune your calculations to get the maximum results.

Once your calculations are producing true results in all (or most) cases, use a looping script to write the data to new FirstName, MiddleName and LastName text fields using Set Field [FirstName; new FirstNameCalc formula], etc. To get your started, here are basic text calculations for the example names you provided:

FirstName: LeftWords(NameField; 1)

MiddleName: If( not PatternCount(MiddleWords(NameField; 2; 2); "-"); MiddleWords(NameField; 2;1))

LastName: If(PatternCount(RightWords(NameField; 2); "-"); RightWords(NameField; 2); RightWords(NameField; 1))

I am not a text-calculation master (obviously) but many on this forum are. You would want to adjust (possibly) for things such as: Mr, Mrs, Middle Name, Jr, Sr. and ?? And I have one other suggestion: Don't reformat the names as LastName, FirstName in the same field. After you've split them apart, leave them split; then simply use a calculation to put them back together for various displays. I hope this at least gets you moving forward.

LaRetta

[LaRetta]

Oh. I neglected to tell you how to split the original group of full names. Something like this would work:

You can use the comma to split the names into a separate NameField. So, for the first full name, you could use: Left(ManyNamesField; Position ( ManyNamesField; ", "; 1; 1) -1)

But I would take it even further, as suggested above and I would bet there are better conversion calcs, using Middle() or MiddleValues() instead of Left(). One could use Substitute(ManyNamesField; ", ";

[transpower]

OT: Hey, I have the book by those authors: Feedback Control of Dynamic Systems. (Using some of the material in that book, I wrote a commercial software package, Optimal Control Designer, in Mathcad, MATLAB, and Maple--but, alas, not FileMaker.) Anyway, LaRetta has answered the question.