Skip to main content

Need help with Substitute/If

F

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.

    3 replies

    Kamille_Parks11
    Forum|alt.badge.img+25

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

    IF(
   LEFT(REGEX_REPLACE(Telephone, "[^\\d]", ""), 1) != "0",
   "0"
) & REGEX_REPLACE(Telephone, "[^\\d]", "")

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

    ScottWorld
    A
    Justin_Barrett
    3 people like this
    • ScottWorld
      ScottWorld
    • A
      augmented
    • Justin_Barrett
      Justin_Barrett
    F
    • Fabian_Boudin
    • Author
    • New Participant
    • 1 reply
    • February 1, 2022

    It’s perfect ! Thank you a lot !

    J
    • jacky_joy
    • Participating Frequently
    • 5 replies
    • February 1, 2022
    Kamille_Parks11 wrote:

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

    IF(
   LEFT(REGEX_REPLACE(Telephone, "[^\\d]", ""), 1) != "0",
   "0"
) & REGEX_REPLACE(Telephone, "[^\\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


    Most helpful members this week

    Terms & Conditions