You need to sign in to do that
Don't have an account?
Lorenzo 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
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
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
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 , " ", "") , "-", "") , ")", "") , "(", "") , "+", ""), ".", "")))
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.
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 , " ", "") , "-", "") , ")", "") , "(", "") , "+", ""), ".", "")))
I received an error stating extra ',' after -2)) in the formula
"-", "") , ")", "") , "(", "") , "+", ""), ".", ""))-2)), <<
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
, " ", "") , "-", "") , ")", "") , "(", "") , "+", ""), ".", "")))