How to match phone numbers?
Jun 2, 2021
By default, a Lead.Phone field can contain lots of extra formatting characters: spaces, hyphens and braces (often added by the platform) or indeed just about any character. So querying to find the Lead may fail because of that extra formatting e.g.:
- +1 (111)-(111)-(1111)
- (111)111–1111
One approach is to add a formula field that holds a cleaned up version of the number and match to that.
A series of SUBSTITUTE calls for the most-likely non-numeric characters is all that’s needed in a formula field.
SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(Phone, “+1”, “”), “(“, “”), “)”, “”), “-”, “”), “ “,””), “.”,””), “/”,””)
