function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Lorenzo WilliamsonLorenzo Williamson 

Formula to remove leading zeros and special characters

Hello all

I'm attempting to create a formula field that will strip out any non-numeric characters as well as the two leading zeros you can often find on an international number.

I.E. 00447989.737.134 needs to be 447989737134

To deal with the leading zeros I have IF(BEGINS(ts2__Mobile_Telephone_Number__c,"00"), RIGHT(ts2__Mobile_Telephone_Number__c,11),ts2__Mobile_Telephone_Number__c)  to deal with the leading zeros

To deal with the non-numeric characters I have (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ts2__Mobile_Telephone_Number__c , " ", "") , "-", "") , ")", "") , "(", "") , "+", ""), ".", ""))

What i don't have is a valid way of combining the two, if there is such a way. Any help or advice on this would very much be appreciated, I am still learning!

Thanks
Lorenzo
Best Answer chosen by Lorenzo Williamson
Pankaj_GanwaniPankaj_Ganwani
Hi,

I created the below mentioned formula in my dev org and it seems to be working fine. You can try with this one:

IF(BEGINS(ts2__Mobile_Telephone_Number__c
,"00"), RIGHT((SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ts2__Mobile_Telephone_Number__c
, " ", "") , "-", "") , ")", "") , "(", "") , "+", ""), ".", "")),LEN((SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
ts2__Mobile_Telephone_Number__c
, " ", "") , "-", "") , ")", "") , "(", "") , "+", ""), ".", ""))) - 2),(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
ts2__Mobile_Telephone_Number__c
, " ", "") , "-", "") , ")", "") , "(", "") , "+", ""), ".", "")))

All Answers

Pankaj_GanwaniPankaj_Ganwani
Hi,

Can you please try to use this?

IF(BEGINS(ts2__Mobile_Telephone_Number__c,"00"), RIGHT((SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ts2__Mobile_Telephone_Number__c , " ", "") , "-", "") , ")", "") , "(", "") , "+", ""), ".", "")),11),(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ts2__Mobile_Telephone_Number__c , " ", "") , "-", "") , ")", "") , "(", "") , "+", ""), ".", "")))
Lorenzo WilliamsonLorenzo Williamson
Hi Pankaj,

Thanks, this works very well. 

Do you know if there is a way in which I do not need to specify the length of the field, my tests show that in most cases the number will display correctly, but this will not always be the case for some countries that may contain an extra 1 or 2 digits in their numbers. 
Pankaj_GanwaniPankaj_Ganwani
Hi,

Can you please try with it? I have used LEN function in it:

IF(BEGINS(ts2__Mobile_Telephone_Number__c,"00"), RIGHT((SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ts2__Mobile_Telephone_Number__c , " ", "") , "-", "") , ")", "") , "(", "") , "+", ""), ".", "")),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ts2__Mobile_Telephone_Number__c , " ", "") , "-", "") , ")", "") , "(", "") , "+", ""), ".", ""))-2)),(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ts2__Mobile_Telephone_Number__c , " ", "") , "-", "") , ")", "") , "(", "") , "+", ""), ".", "")))
Lorenzo WilliamsonLorenzo Williamson
Hi Pankaj,

I received an error stating extra ',' after -2)) in the formula

"-", "") , ")", "") , "(", "") , "+", ""), ".", ""))-2)),  <<
 
Pankaj_GanwaniPankaj_Ganwani
Hi,

I created the below mentioned formula in my dev org and it seems to be working fine. You can try with this one:

IF(BEGINS(ts2__Mobile_Telephone_Number__c
,"00"), RIGHT((SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ts2__Mobile_Telephone_Number__c
, " ", "") , "-", "") , ")", "") , "(", "") , "+", ""), ".", "")),LEN((SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
ts2__Mobile_Telephone_Number__c
, " ", "") , "-", "") , ")", "") , "(", "") , "+", ""), ".", ""))) - 2),(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
ts2__Mobile_Telephone_Number__c
, " ", "") , "-", "") , ")", "") , "(", "") , "+", ""), ".", "")))
This was selected as the best answer
Lorenzo WilliamsonLorenzo Williamson
Thank you very much Pankaj, this is working perfectly.