MixedCase ( textinput )

Data entry formatting, company/address field needs to have both UPPER case and Title case.

Be the first to rate this function Log in to vote

Jan Edgecombe - Show more from this author
offroadparking

Sample input:

MixedCase(IBM is international business machines corporation)

Sample output:

IBM is International Business Machines Corporation

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

While(
	[
		~text = textInput;
		~excluded = List( "in";
			"is";
			"of";
			"de";
			"la";
			"a";
			"to";
			"by";
			"on" );
		~result = "";
		~WordsList = Substitute(
			TrimAll( TextFormatRemove( ~text ); 1; 0 );
			" ";
			¶
		);
		~WordCount = ValueCount( ~WordsList );
		~count = 1
	];
	~count ≤ ~WordCount;
	[
		~middle = MiddleWords( ~WordsList; ~count; 1 );
		~isUpper = Exact( ~middle; Upper( ~middle ) );
		~Words = Case(
			~isUpper = 0;
			Case(
				PatternCount( ~excluded; ~middle ) > 0;
				Lower( ~middle );
				Proper( ~middle )
			);
			~isUpper = 1;
			~middle;

		);
		~list = List( ~result; ~Words );
		~result = Substitute( ~list; ¶; " " );
		~count = ~count + 1
	];
	~result
)

Objective:
To improve data entry formatting for names and address, where the data includes capitalised letters.

The problem:
In order to keep text being entered, nicely formatted.
Often during the data input the Title Case lettering would be missed, we used the Proper() function to create Title Case text.
If a company name included capitalised text such as The BBC or IBM then we had to put a space between letters.

B B C News or I B M Ai Dept

It doesn’t look good and it’s difficult to find the word B B C, someone new to the database would type BBC... oops!

BBC news becomes: BBC News IBM ai dept. becomes: IBM Ai Dept.

This formula creates a list, strips out spare spaces, counts the number of words.
Using the While() function each word is checked to see if it is already in Upper case.
The Exact() function is used, as it is case sensitive.
A Case() function applies the Proper() function to a word not capitalised and does nothing to a word already in capitals.
The exceptions list allows you to exclude small words not normally displayed in title case.
As a bonus 1234567890 numbers and special characters !@£$%^&*() are not effected.
They could easily be filtered out as required.

Comments

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

MixedCase ( textinput )

Comments

Support this website.

Support this website.