Skip to main content

Hello people, hope you can help me to solve my problem.


I’m currently using a formula to substitute some character for cleaning my cell :


SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Telephone,".","")," “,”"),"-",""),"(",""),")","")


and after this step , i’m using an other formula :


IF(LEFT(Telephone,1)!=“0”,CONCATENATE(“0”,Telephone),Telephone)


it works perfectly but the question is : how can i do the same thing in one formula ?


For exemple :


(06-07-45)43.56 26 i want 060745435626

(6-07-45)43.56 26 i want 060745435626


Sorry for my english, i’m french :grinning_face_with_smiling_eyes: and thank you for your answers.

Simplify your formula by using REGEX to remove “everything that’s not a digit” (<^\\d])


IF(

   LEFT(REGEX_REPLACE(Telephone, "o^\\d]", ""), 1) != "0",

   "0"

) & REGEX_REPLACE(Telephone, "o^\\d]", "")

^ the above could probably be written more efficiently with a single line of REGEX, but the above will work.

It’s perfect ! Thank you a lot !

Simplify your formula by using REGEX to remove “everything that’s not a digit” (<^\\d])


IF(

   LEFT(REGEX_REPLACE(Telephone, "o^\\d]", ""), 1) != "0",

   "0"

) & REGEX_REPLACE(Telephone, "o^\\d]", "")

^ the above could probably be written more efficiently with a single line of REGEX, but the above will work.



thanks my issue has been fixed.

Reply


Terms & Conditions