fnNameParser ( name ; type )

c. 2012 Drewsol. This CF parses specified name parts (first, last, middle) from a single name field for the flexibility of sorting and/or display. fnNameStraight now handles Spanish names, and some middle-eastern names.

Average rating: 4.0 (41 votes) Log in to vote

Drew Wesley - Show more from this author
Drewsol
https://www.drewsol.com

Sample input:

fnNameParser™ ( "Martin Luther King, Jr." ; "lastfirstm" )

fnNameParser™ ( "Martin Luther King, Jr." ; "initials" )

fnNameStraight ( "Fransisco de la Hoya" ; "lastfirst" )

fnNameParser™ ( "Andrew Smith-Jones" ; "last" )

Sample output:

King, Martin L.

MK

de la Hoya, Fransisco

Smith-Jones

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/*
fnNameParser™ v2.1
Copyright © 2012 Drewsol

GNU Lesser General Public License:  This function is free software: you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.  This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License for more details.  You should have received a copy of the GNU Lesser General Public License along with this program.  If not, see <http://www.gnu.org/licenses/>.

This CF parses specified name parts (first, last, middle) from a single name field for the flexibility of sorting and/or display.  fnNameStraight now handles Spanish names, and some middle-eastern names.

NameParser™ ( name ; type )
 - name = any name field
 - type = "First", "Middle", "Last", "FirstLast", "FirstMLast", "FirstMiddleLast", "FirstL", "FLast", "LastFirst", "LastFirstM", "initials", "initialslong", "namestraight", "prefix", "suffix"

ChangeLog:
   v2.1 - Better support for hyphenated names.
   v2.0 - Support for professional prefixes and suffix extraction.
   v1.9 - Changed name from fnNameStraight™ to fnNameParser™.
   v1.8 - "Miss" prefix included.
   v1.7 - Better handling of single-word names.

Examples:

			Martin Luther King, Jr.:	Francisco de la Hoya:	Andrew Smith-Jones:
	-------------------------------------------------------------------------------------------------------------------------
	namestraight	Martin Luther King		Francisco de la Hoya	Andrew Smith-Jones
	first		Martin			Francisco			Andrew
	middle		Luther						
	last		King			de la Hoya		Smith-Jones
	firstlast		Martin King		Francisco de la Hoya	Andrew Smith-Jones
	firstmlast		Martin L. King		Francisco de la Hoya	Andrew Smith-Jones
	firstmiddlelast	Martin Luther King		Francisco de la Hoya	Andrew Smith-Jones
	firstl		Martin K.			Francisco D.		Andrew S.
	flast		M. King			F. de la Hoya		A. Smith-Jones
	lastfirst		King, Martin		de la Hoya, Francisco	Smith-Jones, Andrew
	lastfirstm		King, Martin L.		de la Hoya, Francisco	Smith-Jones, Andrew
	initials		MK			FD			AS
	initialslong	MLK			FD			AS
*/

Let ( [

   string_sub = "3f2pb1fBkO";

   string_acceptable_characters = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzä ,'-";

   // ------- FILTER 1: Filter Normal Characters, Spaces, and Commas Only (No Periods) -------

   filter1 =
   Substitute (
      Filter ( name ; "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzä ,'-" ) ;
      [ "-" ; string_sub ]
   ) ;

   wordcount_v1 = WordCount (filter1) ;


   // ------- FILTER 2: Exclude Personal and Professional Prefixes (1st Sweep) -------
   filter2 =
   Case (
      LeftWords ( filter1 ; 1 ) = "Mr" or
      LeftWords ( filter1 ; 1 ) = "Miss" or
      LeftWords ( filter1 ; 1 ) = "Mrs" or
      LeftWords ( filter1 ; 1 ) = "Ms" or
      LeftWords ( filter1 ; 1 ) = "Dr" or
      LeftWords ( filter1 ; 1 ) = "PhD" or
      LeftWords ( filter1 ; 1 ) = "MD" or
      LeftWords ( filter1 ; 1 ) = "JD" or
      LeftWords ( filter1 ; 1 ) = "DDS" or
      LeftWords ( filter1 ; 1 ) = "Jr" and wordcount_v1  ≥ 2 or
      LeftWords ( filter1 ; 1 ) = "Sr" or
      LeftWords ( filter1 ; 1 ) = "Sra" or
      LeftWords ( filter1 ; 1 ) = "Srta" or
      LeftWords ( filter1 ; 1 ) = "Lic" or
      LeftWords ( filter1 ; 1 ) = "Sir";
      MiddleWords ( filter1 ; 2 ; wordcount_v1 - 1 ) ;
      filter1
   ) ;

   wordcount_v1 = WordCount ( filter2 ) ;

   prefix =
   Case (
      WordCount ( filter1 ) > WordCount ( filter2 );
      LeftWords ( filter1 ; 1 );
   );


   // ------- FILTER 3: Exclude Personal and Professional Suffixes (2nd Sweep) -------
   filter3 =
   Case (
      RightWords ( filter2 ; 1 ) = "Jr" and wordcount_v1  ≥ 2 or
      RightWords ( filter2 ; 1 ) = "Sr" or
      RightWords ( filter2 ; 1 ) = "PhD" or
      RightWords ( filter2 ; 1 ) = "MD" or
      RightWords ( filter2 ; 1 ) = "JD" or
      RightWords ( filter2 ; 1 ) = "DDS" or
      RightWords ( filter2 ; 1 ) = "MBA" or
      RightWords ( filter2 ; 1 ) = "CPA" or
      RightWords ( filter2 ; 1 ) = "CEO" or
      RightWords ( filter2 ; 1 ) = "CFO" or
      RightWords ( filter2 ; 1 ) = "CTO" or
      RightWords ( filter2 ; 1 ) = "CTO" or
      RightWords ( filter2 ; 1 ) = "COO";
      MiddleWords ( filter2 ; 1 ; wordcount_v1 - 1 ) ;
      filter2
   ) ;

   wordcount_v1 = WordCount ( filter3 ) ;

   firstcomma = Position ( filter3 ; "," ; 1 ; 1 ) ;

   lastword = Right ( name ; Length ( name ) - Position ( name ; " " ; Length ( name ) ; -1 ) ) ;

   suffix =
   Case (
      WordCount ( filter2 ) > WordCount ( filter3 );
      RightWords ( filter2 ; 1 );
   );


// ------- FILTER 4: Exclude Roman Numeral Suffixes and Place Name In Sequential Order (First Middle Last) -------

   romannumeral =
   Case (
      RightWords ( filter3 ; 1 ) = "I" and PatternCount ( lastword ; "." ) = 0 or
      RightWords ( filter3 ; 1 ) = "II" or
      RightWords ( filter3 ; 1 ) = "III" or
      RightWords ( filter3 ; 1 ) = "IV" or
      RightWords ( filter3 ; 1 ) = "V" and PatternCount ( lastword ; "." ) = 0 or
      RightWords ( filter3 ; 1 ) = "VI" or
      RightWords ( filter3 ; 1 ) = "VII" or
      RightWords ( filter3 ; 1 ) = "VIII" or
      RightWords ( filter3 ; 1 ) = "IX" or
      RightWords ( filter3 ; 1 ) = "X"and PatternCount ( lastword ; "." ) = 0 ;
      "yes" ; "no"
   ) ;

   //Starting Word After Comma
   swac =
   WordCount ( Left ( filter3 ; Position ( filter3 ; "," ; 1 ; 1) ) ) + 1 ;

   filter4 =
   Case (
      PatternCount ( filter3 ; "," ) > 0 and romannumeral = "no" ;
      MiddleWords ( filter3 ; swac ; wordcount_v1 - 1 ) & " " & Middle ( filter3 ; 1 ; firstcomma - 1 ) ;
      PatternCount ( filter3 ; "," ) > 0 and romannumeral = "yes" ;
      MiddleWords ( filter3 ; swac ; wordcount_v1 - swac ) & " " & Middle ( filter3 ; 1 ; firstcomma - 1 ) ;
      PatternCount ( filter3 ; "," ) = 0 and romannumeral = "yes" ;
      LeftWords ( filter3 ; 1) & " " & MiddleWords (filter3 ; 2 ; wordcount_v1 - 2 ) ;
      filter3
   ) ;

   wordcount_v1 = WordCount ( filter4 ) ;

   generational_suffix =
   Case (
      WordCount ( filter3 ) > WordCount ( filter4 );
      RightWords ( filter3 ; 1 );
   );


   // ------- FILTER 5: Reconstruct Name Parts -------

   // ------- Disect Name Parts -------

   w1 =
   Case (
      Length ( MiddleWords ( filter4 ; 1 ; 1 ) ) = 1 ;
      Replace ( MiddleWords ( filter4 ; 1 ; 1 ) ; 1 ; Length ( MiddleWords ( filter4 ; 1 ; 1 ) ) ; MiddleWords ( filter4 ; 1 ; 1 ) & "." ) ;
      MiddleWords ( filter4 ; 1 ; 1 )
   ) ;

   w2 =
   Case (
      Length ( MiddleWords ( filter4 ; 2 ; 1 ) ) = 1 ;
      Replace ( MiddleWords ( filter4 ; 2 ; 1 ) ; 1 ; Length ( MiddleWords ( filter4 ; 2 ; 1 ) ) ; MiddleWords ( filter4 ; 2 ; 1 ) & "." ) ;
      MiddleWords ( filter4 ; 2 ; 1 )
   ) ;

   w3 =
   Case (
      Length ( MiddleWords ( filter4 ; 3 ; 1 ) ) = 1 ;
      Replace ( MiddleWords ( filter4 ; 3 ; 1 ) ; 1 ; Length ( MiddleWords ( filter4 ; 3 ; 1 ) ) ; MiddleWords ( filter4 ; 3 ; 1 ) & "." ) ;
      MiddleWords ( filter4 ; 3 ; 1 )
   ) ;

   w4 =
   Case (
      Length ( MiddleWords ( filter4 ; 4 ; 1 ) ) = 1 ;
      Replace ( MiddleWords ( filter4 ; 4 ; 1 ) ; 1 ; Length ( MiddleWords ( filter4 ; 4 ; 1 ) ) ; MiddleWords ( filter4 ; 4 ; 1 ) & "." ) ;
      MiddleWords ( filter4 ; 4 ; 1 )
   ) ;

   w5 =
   Case (
      Length ( MiddleWords ( filter4 ; 5 ; 1 ) ) = 1 ;
      Replace ( MiddleWords ( filter4 ; 5 ; 1 ) ; 1 ; Length ( MiddleWords ( filter4 ; 5 ; 1 ) ) ; MiddleWords ( filter4 ; 5 ; 1 ) & "." ) ;
      MiddleWords ( filter4 ; 5 ; 1 )
   ) ;

   filter5 =
   Case (
      wordcount_v1 = 5 ;
      w1 & " " & w2 & " " & w3 & " " & w4 & " " & w5 ;
      wordcount_v1 = 4 ;
      w1 & " " & w2 & " " & w3 & " " & w4 ;
      wordcount_v1 = 3 ;
      w1 & " " & w2 & " " & w3 ;
      wordcount_v1 = 2 ;
      w1 & " " & w2 ;
      wordcount_v1 = 1 ;
      w1 ;
      filter4
   ) ;


   // ------- FILTER 6: Exclude Personal and Professional Prefixes and Suffixes (3rd Sweep) -------
   filter6 =
   Case (
      RightWords ( filter5 ; 1 ) = "Jr" and wordcount_v1  ≥ 2 or
      RightWords ( filter5 ; 1 ) = "Sr" or
      RightWords ( filter5 ; 1 ) = "PhD" or
      RightWords ( filter5 ; 1 ) = "DDS";
      MiddleWords ( filter5 ; 1 ; wordcount_v1 - 1 ) ;
      filter5
   ) ;

   wordcount_v1 = WordCount ( filter6 ) ;

   suffix =
   Case (
      suffix <> "";
      suffix;
      WordCount ( filter6 ) > WordCount ( filter5 );
      RightWords ( filter5 ; 1 );
   );


   // ------- VARIATIONS: -------


   //   Function Title (Straight Name)
   namestraight = Substitute ( filter6 ; [ string_sub ; "-" ] ) ;


   //   Single Name Parts (first, middle, last)
   first = Substitute ( LeftWords (filter6 ; 1) ; [ string_sub ; "-" ] ) ;

   last =
   Case (
      MiddleWords ( filter6 ; wordcount_v1 - 2; 1 ) = "de" and
      MiddleWords ( filter6 ; wordcount_v1 - 1; 1 ) = "la" ;
      Substitute ( RightWords ( filter6 ; 3 ) ; [ string_sub ; "-" ] ) ;
      MiddleWords ( filter6 ; wordcount_v1 - 1; 1 ) = "de" or
      MiddleWords ( filter6 ; wordcount_v1 - 1; 1 ) = "del" or
      MiddleWords ( filter6 ; wordcount_v1 - 1; 1 ) = "le" or
      MiddleWords ( filter6 ; wordcount_v1 - 1; 1 ) = "la" or
      MiddleWords ( filter6 ; wordcount_v1 - 1; 1 ) = "di" or
      MiddleWords ( filter6 ; wordcount_v1 - 1; 1 ) = "don" or
      MiddleWords ( filter6 ; wordcount_v1 - 1; 1 ) = "al";
      MiddleWords ( filter6 ; wordcount_v1 - 1; 2 ) ;
      Substitute ( RightWords ( filter6 ; 1 ) ; [ string_sub ; "-" ] )
   ) ;

   middle =
   Let ( [
      lastraw = Substitute ( last ; [ "-" ; string_sub ] ) ;
      wordcount_middle = wordcount_v1 - ( WordCount ( first ) + WordCount ( lastraw ) )
      ] ;
      Case (
         Length ( MiddleWords ( filter6 ; 2 ; wordcount_middle ) ) = 1 ;
         MiddleWords ( filter6 ; 2 ; wordcount_middle ) & "." ;
         wordcount_v1 ≥ 3 ;
         MiddleWords ( filter6 ; 2 ; wordcount_middle ) ;
         ""
      )
   ) ;


   //   Recompilations
   firstlast =
   Case (
      WordCount ( filter6 ) = 1 ;
      filter6 ;
      first & " " & last
   ) ;

   firstmlast =
   Case (
      WordCount ( filter6 ) = 1 ;
      filter6 ;
      middle <> "" ;
      first & " " & Left (middle ; 1 ) & "." & " " & last ;
      first & " " & last
   ) ;

   firstmiddlelast =
   Case (
      WordCount ( filter6 ) = 1 ;
      filter6 ;
      Substitute ( filter6 ; [ string_sub ; "-" ] )
   ) ;

   firstl =
   Case (
      WordCount ( filter6 ) = 1 ;
      filter6 ;
      first & " " & Upper ( Left ( last; 1 ) ) & "."
   ) ;

   flast =
   Case (
      WordCount ( filter6 ) = 1 ;
      filter6 ;
      Left ( first; 1 ) & "." & " " & last
   ) ;

   lastfirst =
   Case (
      WordCount ( filter6 ) = 1 ;
      filter6 ;
      last & "," & " " & first
   ) ;

   lastfirstm =
   Case (
      WordCount ( filter6 ) = 1 ;
      filter6 ;
      middle <> "" ;
      last & "," & " " & first & " " & Left (middle ; 1 ) & "." ;
      last & "," & " " & first
   ) ;


   //   Initials

   initials =
   Upper (
      Case (
         WordCount ( filter6 ) = 1 ;
         Left ( filter6 ; 1 ) ;
         wordcount_v1 = 1 ; Left ( filter6 ; 1 ) ;
         Left ( first ; 1 ) & Left ( last ; 1 )
      )
   ) ;

   initialslong =
   Upper (
      Case (
         WordCount ( filter6 ) = 1 ;
         Left ( filter6 ; 1 ) ;
         wordcount_v1 = 1 ; Left ( filter6 ; 1 ) ;
         Left ( first ; 1 ) & Left ( middle ; 1 ) & Left ( last ; 1 )
      )
   )

   ] ;

   Case (
      //   Parts
      type = "First"; first ;
      type = "Middle" ; middle ;
      type = "Last" ; last ;
      //   Recompilations
      type = "FirstLast" ; firstlast ;
      type = "FirstMLast" ; firstmlast ;
      type = "FirstMiddleLast" ; namestraight ;
      type = "FirstL" ; firstl ;
      type = "FLast" ; flast ;
      type = "LastFirst" ; lastfirst ;
      type = "LastFirstM" ; lastfirstm ;
      type = "NameStraight" ; namestraight ;
      //   Initials
      type = "initials" ; initials ;
      type = "initialslong" ; initialslong;
      //   Prefixes / Suffixes
      type = "generational suffix" ; generational_suffix;
      type = "prefix" ; prefix;
      type = "suffix" ; suffix
   )

) /*End Let*/

c. 2012 Drewsol. This CF (procedurally) parses specified name parts (first, last, middle) from a single name field for the flexibility of sorting and/or display. fnNameStraight now handles Spanish names, and some middle-eastern names.

Comments

		DZakary, Toronto, Canada May 26, 2009
Cool function. I've done similar things, but in a much more brute force manner. Any thoughts on how to handle hyphenated last names (Andrew Smith-Jones)?

		Drew Wesley, Drewsol May 27, 2009
I updated the function to support hyphenated last names. Thanks for your feedback.

		DZakary, Toronto, Canada May 27, 2009
Cool. Thanks for the update.

		Drew Wesley, Drewsol May 27, 2009
You're very welcome - thanks again for putting it to the test. Enjoy it!

		Michael Ashley, San Francisco Aug 9, 2010
Great function, thank you. I was having a problem with diacritics. I added Ã¤ to the normal character list and presto, all gud. Thought I'd send the tip along. This helped us out with our Finnish and Swedish friends.

		Drew Wesley, Drewsol Feb 13, 2012
I've updated this custom function to include more recent user requests.

		Reggie, Inverness, FL Oct 22, 2012
I can't seem to make this work with hypenated names. It never includes the hyphen and doesn't format the name correctly.

		Will, Little Rock, AR Jan 7, 2013
Great function! Thank you for sharing your hard work.

		Drew Wesley, Drewsol Oct 8, 2013
I added a fix that keeps hyphenated names intact.

		Scott C, Saluda, North Carolina Sep 14, 2015
How does this function know that "de la Hoya" is the last name? I import a spreadsheet that contains a field, FullName that includes some records in which the last two words in FullName are the last name so this function would be helpful but I'm curious to understand how it works. For example, assume FullName = John Van Heusen. The last name is "Van Heusen."

		Tjebbe van Tijen, IMP Feb 11, 2016
Good BUT why you throw away the . (points) of First name(s) ? or did I miss on something, is the type parameter a single one, of several? I see no functionality to retain the points... Example P.H.J. Cuypers should become Cuypers, P.H.J. leaving out the points (is NOT done in any library or documentation environment) Your function is greatly needed, but please react...or I have to continue trying to write my own one... Thank you

		Tjebbe van Tijen, IMP Feb 11, 2016
Looked into your function a bit more... I have added the . (point) as something to retain that helped a bit, but still it does what seems to be a typical FPM flaw P.H.J. Cuypers with the parameter LastFirstM becomes: Cuypers, P.H.J indeed the point behind the J in the original has gone also I noticed that you may live in the USA without people having names like me Tjebbe Willem van Tijen you rfuncion makes this of it Tijen, Tjebbe W. Can NOT be used... so maybe I have been to optimistic one needs many, many rules to handle names (I have been an academic librarin for decades... so I go back to my own attempt... though of course I appreciate your effort...

		Drew Wesley, Drewsol Mar 19, 2016
Hi Tjebbe van Tijen, I didn't get notified of your post until I checked randomly (I changed my email a year ago). Let me know if you still need help.

		Oct 27, 2019
Drew : The fnNameParser function does not work with dashes... Oscar de-la-horta Hernandez - comes out funky Tried to add a dash or minus sign to the accepted characters and it didn't work... What other modification can one make to the function so it works right...

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

fnNameParser ( name ; type )

Comments

Support this website.

Support this website.