In keeping with a text parsing theme I seem to have going here, this week’s function is the fabulous Filter function. It’s a text strainer that only permits the characters you specify to come out the other end. But you must be cautious, Filter behaves in very specific ways that might trip you up. Here’s the basic format.
Here, our text to examine is a phone number and a name. Our characters to include are the ten digits zero through nine. Filter will examine each character in the text to the left and retain only those that appear in the filter text to the right. All other characters are caught in the filter and don’t get through. So, as you’ve probably worked out by now, the result of the above calculation is “8675309”.
Filter is particularly handy in cleaning up scenarios where numbers and text have been combined in a single field. When migrating old data and attempting to apply some degree of normalization, teasing out the phone type from the actual phone number is a pretty common task. The next example shows how the Filter function can be of assistance.
Here we can see how Filter allows us to extract two pieces of information from a single field and move each into its own discrete field. To be fair, data cleanup is rarely as clean as you see here. In a more realistic scenario, you’ll be dealing with a hodgepodge of text before and after the phone numbers, leading ones before the area code, and highly inconsistent formatting. Filter is just one of the text functions you’ll use to clean up a mess like that.
Important things to keep in mind about Filter.
- Filter is case sensitive. Filter ( “Hello hello” ; “hello ” ) will return “ello hello”.
- The order of the characters to filter is irrelevant. Filter ( “Hello hello” ; “oh le” ) will still return “ello hello”.
- Spaces and punctuation are treated just like letters and numerals. If you want punctuation in your results, you’ll have to include it in your characters to filter.
Bonus Frivolity
Inspired by something my friend Lynn spotted at the most recent DevCon, here’s a completely silly use of Filter. If you’re a Futurama fan, see if you can figure it out before you pop it into your Data Viewer.
Filter ( “You Bum, lend of her – wish – grow wheat” ; “daring-Bets” )
Subscribe to our blog feed