Text parsing pops up a lot when migrating data from an old system to a new one, or even when you realize that your data just needs some grooming. Whatever the impetus, Left, Right, and Middle are the cornerstones of text manipulation in FileMaker. All three simply extract a portion of the text you supply. Let’s start with Left and Right.
Both Left and Right take two parameters. The first is some text either as a string in quotes as shown above, the name of a field that contains text, or an expression with a text result. The second parameter is the number of characters you want returned from the text in the first parameter. For Left FileMaker starts at the first character on the left and returns the number of characters specified. Right, as I’m sure you’ve worked out by now, starts at the opposite side. Middle is just a tad more involved.
Middle requires a third parameter. The first parameter is the source text as described above. The second parameter is the position where you want FileMaker to start extracting text. In the example above I use the number 5 because I want to pull out the telephone number prefix which starts with the fifth character in the string, following the three digit area code and the first hyphen. The final parameter specified the number of characters to be returned.
Cleaning up phone numbers is a classic data migration chore, which is why I chose it for the examples here. Unfortunately, when you have to groom data it’s rarely as neatly formatted as shown here. But in some coming posts, I’ll present a few more text manipulation functions you can add to your kit.
Subscribe to our blog feed