FM Function of the Week: LeftWords, RightWords, and MiddleWords

Last week we looked at the Left, Right, and Middle text parsing functions. These are extremely useful when you need to parse out highly regular data like, say, a telephone number where you can count on the first three characters to be an area code (in North America anyway). But when you’ve got less rigidly structured information like names, you can’t be sure how long or short any given name might be. This is where LeftWords, RightWords, and MiddleWords can save you some significant calculation gymnastics. Rather than forcing you to calculate where one word ends and the next begins, the Words functions figure it out for you.

left- right- and middlewords

All three functions require some text for their first parameter; this can be a plain string of text in quotes, the name of a field that contains text, or any expression with a text result. For the Left and Right variants, the only other parameter is the number of words to be included. MiddleWords requires the number of the word you wish to start with followed by the number of words to include. The table below lays out several examples for how FileMaker treats data thrown at these functions.

Examination of above examples reveals that FileMaker’s definition of a word depends very much on context. The boundary between words is almost always a space character and these are handled exactly like you’d expect. However, most other punctuation characters are treated as word breaks too, as lines 3 and 4 illustrate. A hyphen breaks words when it has alphabetical characters around it, but not when surrounded by numbers. I personally find it counter-intuitive that FileMaker considers an underscore a word break, but not a period. Any punctuation followed by a space will be discarded from the calculation result as you can see with the “Mr. F” and telephone number examples.

You can beat the system using non-breaking spaces. A non-breaking or hard space looks just like a normal space, but gets special treatment. To create one using a Mac, hold down the option key when pressing the spacebar. On Windows, hold down the alt key and, using your numeric keypad, type 0160. When you release the alt key, the space should appear. FileMaker will recognize them and treat text with non-breaking spaces as a single word.

Line breaks are also word breaks in FileMaker’s world and behave as you’d expect with the three Words functions, but next week we’ll look at a third variety of Left/Right/Middle function that specifically deal with line breaks.

This entry was posted in FileMaker 13, Function of the Week. Bookmark the permalink.