Field formula to remove spaces, dashes, and special characters from value - Answers - Salesforce Trailblazer Community
Ask Search:
Fariya KhanFariya Khan 

Field formula to remove spaces, dashes, and special characters from value

Hi. I'm trying to find a formula that can remove spaces or special characters from a phone number field.

i.e. with a web form a person can submit
000 000 0000  and the formula field would show 0000000000
or (000) 000-0000 and the formula field would still show 0000000000
does anyone know how to do this?

If it's possible to remove a leading 1 that would also be amazing.

Thanks!

Peter
Best Answer chosen by Miglena (Salesforce.com) 
Zachary SingerZachary Singer
Hi Peter,
I just ran into this requirement also and solved it using nested SUBSTITUTE() functions. I did this because SUBSTITUTE() only allows for 3 parameters (the field, what you are replacing, and what you are replacing it with). This formula removes dashes, parentheses, and spaces:

SUBSTITUTE( 
SUBSTITUTE( 
SUBSTITUTE( 
SUBSTITUTE(Phone, "(", ""), ")", ""), " ", ""), "-", "")

All Answers

Steve MolisSteve Molis
For that you'd use a combination of the FIND and SUBSTITUTE functions

https://help.salesforce.com/HTViewHelpDoc?id=customize_functions.htm&language=en_US
Fariya KhanFariya Khan
Thanks Steve. That definitely looks like the right track but I'm not sure how to properly write the function.

Based on the example I used

SUBSTITUTE( Phone__c , LEFT(Phone__c, FIND(" ", Phone__c)), "")

But that only finds the first instance of '1' in the number then erases it and all characters to the left of it. Could you please help me with writing a function which would just replace all ' ''s?
Steve MolisSteve Molis
Personally I would hit this at the front end with a REGEX that blocks non-numeric values, cleaning it up on the back end is a Royal PITA (and I'm not a fan of Monarchy)
Zachary SingerZachary Singer
Hi Peter,
I just ran into this requirement also and solved it using nested SUBSTITUTE() functions. I did this because SUBSTITUTE() only allows for 3 parameters (the field, what you are replacing, and what you are replacing it with). This formula removes dashes, parentheses, and spaces:

SUBSTITUTE( 
SUBSTITUTE( 
SUBSTITUTE( 
SUBSTITUTE(Phone, "(", ""), ")", ""), " ", ""), "-", "")
This was selected as the best answer
Ben PattersonBen Patterson
Is there a regex function to remove anything that's not a letter or number?  I currently check the name, address, and city fields for special characters that I know and replace via substitute, but one recently slipped through via cut/paste.  It looks like a dash, but displayed as a � when exported to my ERP system.
Luis HerreraLuis Herrera
I was looking for the same solution and Zach's suggestion worked flawlessly. Thanks!!
Bruce StewartBruce Stewart
@BZ Zaveri:
Can you use replaceAll in a FLOW FORMULA?  I'm trying this, and getting "extra period" when trying to save.  ({!varStringToBeStripped}).replaceAll('<[^>]+>',' ')
BZ ZaveriBZ Zaveri
Just use Zach Singer's answer to substitute characters in Flow. I've deleted my answer because you can't use replaceall in formulas.
Mendy EzaguiMendy Ezagui
Thanks
David NizenDavid Nizen
Awesome. Thanks Zach Singer!