SmarterProper ( inText ; prevCase ; textSoFar ; prefixList ; exceptions )
Similar to the Proper function, but handles names with prefixes better
Average rating: 2.6 (211 votes) Log in to vote
|
David Tremmel - Show more from this author |
Function definition: (Copy & paste into FileMaker's Edit Custom Function window)
** UPDATED on 7/11/16
Updated and corrected the examples, and fixed the bug noted by Rob
** UPDATED on 6/24/13
finally got around to fixing the bug noted by Lindee - apologies to all who had problems
** UPDATED on 4/30/12 to:
added a way to enter an exception list of words that would be left exactly as entered
** UPDATED on 6/15/09 to:
(1) really fix a bug affecting formatting around apostrophes
(2) fix a bug that gave incorrect results if there were multiple spaces between words
**
** UPDATED on 6/3/08 to:
(1) fix a bug affecting formatting around apostrophes
(2) add the ability to capitalize after hyphens
(3) add the ability to specify that the word start with a lowercase letter
**
SmarterProper is designed to handle names better than the standard Proper function. It returns text with the first letter of each word capitalized - like Proper does - but unlike Proper, it also:
(1) keeps any letters in the middle of words capitalized (if they were preceeded by a lowercase character in the original);
(2) capitalizes letters after an apostrophe (for names like O'Malley) - but only if the remaining text is longer than one character (to correctly format words like Didn't and It's);
(3) capitalizes the first letter of the second part of a hyphenated name;
(4) capitalizes letters after any prefixes you specify (e.g., Mc, Mac).
Syntax: SmarterProper( inText; prevCase; textSoFar; prefixList )
inText = the original text
prevCase = a number representing the case of the preceeding character (for recursion); it should be set to 2 to capitalized the first character of the text, or 0 if you want the first character of the text to be lowercase
textSoFar = the text string at this piont in the recursion; it should be set to empty when the function is called
prefixLIst = list of prefixes that should be followed by a capital letter (a return-delmiited list)
Examples:
SmarterProper ( "MACDONALD"; 2; ""; "Mc¶Mac" ) returns MacDonald
SmarterProper ( "LaMond"; 2; ""; "Mc¶Mac" ) returns LaMond (rather than Lamond)
SmarterProper ( "o'shea"; 2; ""; "Mc¶Mac" ) returns O'Shea
SmarterProper ( "SMITH-THOMAS"; 2; ""; "Mc¶Mac" ) returns Smith-Thomas
Comments
 |
Lee Smith Aug 13, 2010 |
|
| Does anyone review the reviews to catch SPAMM? | ||
 |
Brian Dunning, BrianDunning.com Aug 13, 2010 |
|
| Yes. | ||
 |
Katy Butterworth, New Zealand Sep 15, 2010 |
|
| This was an excellent quick fix to names that were being entered via a website. Many thanks for sharing. | ||
|
Jeff, Los Gatos Dec 19, 2010 |
|
| Have you ever handled acronyms which need to be all caps. This is in regards to our effort to account for known company names such as ITT? | ||
|
Nik, Cygnet, Tasmania AU Apr 29, 2012 |
|
| Thanks so much for this. | ||
|
David Tremmel Apr 30, 2012 |
|
| This custom function was updated on 4/30/12. You can now include a list of exceptions - words/names that will be left exactly as entered. | ||
|
Lindee, Tucson, AZ Oct 4, 2012 |
|
| Good function however: SmarterProper ( "o'shea"; 2; ""; "Mc¶Mac" ) actually returns O'shea The problem appears to be in the formula for newCase. The third ( test ; result ) parameter appears to be missing a result. I don't think you intended it to be: firstChar = "'" and Length ( inText ) > 2; firstChar = "'" and Middle ( inText; 2; 1 ) = " "; A 2 seems to work: newCase = Case ( prevCase = 0 and IsEmpty ( textSoFar) ; 0 ; firstChar = " " or firstChar = "-" or not IsEmpty ( FilterValues ( textSoFar; prefixList ) ) ; 2 ; firstChar = "'" and Length ( inText ) > 2 ; 2 ; firstChar = "'" and Middle ( inText ; 2 ; 1 ) = " " ; 2 ; Exact ( firstChar; Upper ( firstChar ) ) ) |
||
 |
Tobias Sjögren Jun 5, 2013 |
|
| I've used this function for the past few years, it's great. Now, I returned to this page to find that it has an update - the thing is that it doesn't work. When I try to press OK from the Edit Custom Function window it says: "There are too many parameters in this function." This is the line that it stops at: "prevCase = 2; Upper ( firstChar ) & SmarterProper ( newText; 1; textSoFar; prefixList; exceptions );" |
||
|
David Tremmel Jun 24, 2013 |
|
| Finally got around to fixing the bug noted by Lindee... | ||
|
Annette, Ireland Nov 4, 2013 |
|
| I can't even get this to add to the custom functions. It is telling me it's missing operators when I copied and pasted exactly from the above. | ||
|
Ryan, NY Jul 1, 2015 |
|
| I've been trying to use LLC as an exception and it does not prevent it from improperly formatting it. | ||
|
Peter, Bristol Aug 1, 2015 |
|
| WORKS NICELY - thanks succeeds with Mcwhirter-o'shea to translate as McWhirter-O'Shea but fails with o'shea-mcwhirter - translates as O'Shea-Mcwhirter |
||
 |
Rob, Monk Media Jan 2, 2016 |
|
| This function is great. Thank you! I took it one step further and added a global field for prefix and one for exceptions. I've added each exception or prefix followed by a carriage return. Then reference the field in the calculation instead of typing out all the prefix and exceptions into the calculation. This makes it easier and quicker to add new prefix and exceptions as you come across them, also if you use the function in several scripts you don't have to update all of the scripts. |
||
|
Rob, Monk Media May 8, 2016 |
|
| I found an odd bug, it does correct the 's at the end of a word if there is another word after it. Example: SILLY SALLY'S returns--> Silly Sally's (which is perfect) SALLY'S SILLY returns--> Sally'S Silly Any thoughts? Other than this I love this function, thanks for your help. |
||
 |
Eric Jun 15, 2019 |
|
| This is great! Thank you! | ||
 |
Ann Hamel Jun 10, 2021 |
|
| This function is exactly what I am looking for. I have created the Custom Function, but being new to this, I am not sure about where to store the prefixList. Can someone point me in the right direction? My end goal is to apply this as Conditional Formatting for name fields that my team enters. I've already applied the Proper case and just now realized it wiped out all the capital letters inside the names with "Mc", etc. | ||
|
David Tremmel Jun 10, 2021 |
|
| Ann, the prefix list can be part of the function call (the "Mc¶Mac" in the example), or you could store it in a variable (local, or global as suggested by Rob) and reference that variable in the function call. | ||
|
Ann Hamel Jun 10, 2021 |
|
| Maybe I need to back up a few more steps. I copy from above, paste it in the new Custom Function I create, list the parameters - what is my next step? I want to apply this in the Conditional Formatting for a Last Name field. I tried creating fields in the table to reference for the textSoFar, prevCase, prefixList, and exceptions, but I know that isn't right. Clearly I am missing something(s)! | ||
|
Ann Hamel Jun 10, 2021 |
|
| Maybe I need to back up a few more steps. I copy from above, paste it in the new Custom Function I create, list the parameters - what is my next step? I want to apply this in the Conditional Formatting for a Last Name field. I tried creating fields in the table to reference for the textSoFar, prevCase, prefixList, and exceptions, but I know that isn't right. Clearly I am missing something(s)! | ||
|
David Tremmel Jun 10, 2021 |
|
| You cannot change case using Conditional Formatting, only things like color, fill, style, etc. You would need to use this function in a calculation. You could use it in the field's Calculated Value option (use the Options button in the field definition) to automatically change whatever was entered, like: SmarterProper ( Self ; 2; ""; "Mc¶Mac"; "ITT¶Macintosh" ). Alternately you can have a second, calculated field that you would use for display. For example, you could have a text field called "nameEntry" for data entry, and a calculated field called "nameDisplay" with the formula below for display: SmarterProper ( nameEntry; 2; ""; "Mc¶Mac"; "ITT¶Macintosh" ). In both cases you would want to use your own text for the prefixList and exceptions in the formula. |
||
|
Ann Hamel Jun 10, 2021 |
|
| Maybe I need to back up a few more steps. I copy from above, paste it in the new Custom Function I create, list the parameters - what is my next step? I want to apply this in the Conditional Formatting for a Last Name field. I tried creating fields in the table to reference for the textSoFar, prevCase, prefixList, and exceptions, but I know that isn't right. Clearly I am missing something(s)! | ||
|
David Tremmel Jun 10, 2021 |
|
| You cannot change case using Conditional Formatting, only things like color, fill, style, etc. You would need to use this function in a calculation. You could use it in the field's Calculated Value option (use the Options button in the field definition) to automatically change whatever was entered, like: SmarterProper ( Self ; 2; ""; "Mc¶Mac"; "ITT¶Macintosh" ). Alternately you can have a second, calculated field that you would use for display. For example, you could have a text field called "nameEntry" for data entry, and a calculated field called "nameDisplay" with the formula below for display: SmarterProper ( nameEntry; 2; ""; "Mc¶Mac"; "ITT¶Macintosh" ). In both cases you would want to use your own text for the prefixList and exceptions in the formula. |
||
|
Ann Hamel Jun 10, 2021 |
|
| @ David - Thank you for your response. Of course, that is precisely where I had set my Proper (last name) calculate value option. Apparently, I had first tried it in Conditional Formatting and never removed it. Additionally - I apologize for the repeated comments. Every time I refreshed my browser it submitted the comment again. I will have to look at this fresh tomorrow. A quick test didn't even do Proper case which I will take as a starting spot in the morning. Again, thank you! |
||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.