Substitute ( Upper ( Substitute(Self;[" AND ";" and "] ; [" And ";" and "] ; [" aND ";" and "] ; [" ANd ";" and "] ; [" anD ";" and "] ; [" AnD ";" and "] ; [" aNd ";" and "]) ) ; " AND " ; " and " )
Come to think of it, you can use
Left ( Substitute ( Upper ( self & " ") ; " AND " ; " and " ) ; Length ( self ) )
Adding the left function plus appending an added space handles the special case where "and" is the last word in self.
Yes....Once again, I wonder why I try to figure these things out myself. I appreciate your knowledge and your willingness to share! Perhaps an "Idiot's Guide to Filemaker" would be a good starting place for me.
And after even more thought:
Trim ( Substitute ( Upper ( self & " ") ; " AND " ; " and " ) )
is even simpler and less cryptic...
Okay, so while we're at it, suppose, in addition to the above, I also wanted any occurances of "MC" to be "Mc" if they were the first two letters of a word. I figured out how to do it if it's anywhere, just not when it's the first two letters.
Hmm, substitute won't work for that. If you have FileMaker Advanced, you could devise a recursive custom function for this.
Is the text in this field names separated by and?
HARRY and DAVID?
If so, I'd probably use separate fields for the names (Possibly separate, related records) and use a calculation field to combine them with "and".
And I don't think you can use calculations to fix all the capitalization issues with names as they aren't capitalized according to consistent, predicable patterns.
But then maybe this isn't name data at all....
Yes, it is Names Data....usually just one name, but occasionally two, hence, the "and". The "Mc" would be rare, and it's not that big of a deal. I was just hoping it would be as easy as the other solutions.
Thanks for your help.
With separate fields for the individual names, you can use an auto-enter calc like this:
If ( Left ( Self ) = "mc" ; "Mc" & Upper ( Right ( self ; Length ( self ) - 2 ) ) ; Upper ( Self ) )
Then you can use this expression to combine the two names:
Name1 & " and " & Name2