Q: I want to clean up e-mail addresses I've imported into various FileMaker Pro files. The scripts I've written search for matching e-mail addresses and some organizations have different names attached to the same address. For instance, if I search for the address info@wowserrecords.com, there are several people at Wowser Records so I have an array of addresses such as:
Jenny <info@wowserrecords.com>
Admin <info@wowserrecords.com>
Garry <info@wowserrecords.com>
I want to perform a simple substitution that looks for and replaces all e-mail records to just what's between the "<" and ">". I know this is trivial, but I've goofed it up so far. Many thanks for your help.
-- Dave Mac
A: To parse just the e-mail address out of these values, you have to find the position of the two "delimiters" and use the Middle function to return everything between them. If you're using FileMaker Pro 7 or newer, use the Let function to make the syntax simple and clear:
Let ([
Start = Position (Email ; "<" ; 1; 1) ;
End = Position (Email ; ">" ; 1; 1)
];
Middle ( Email ; Start + 1 ; End – Start - 1 )
)
For example, say the e-mail field contained "fred <fred@xyz.com>". In this case, Start would equal 6 (the position of the first "<"), and End would equal 19 (the position of the first ">"). So starting at the character after the "<", take a slice equal to the number of characters between the delimiters: Middle (Email ; 7 ; 12) returns "fred@xyz.com".
If your records don't always contain the greater than and less than format, you'll want to put in a conditional test or other logic to handle those records correctly. And before you run a big replace operation on your data, we'd recommend perfecting your function in a calc field so you can tweak it if necessary.