7 Replies Latest reply: Sep 12, 2017 9:20 AM by Anand Chouhan RSS

    Separating area code from phone number into new field

    Ryan Minaker

      Hi everyone,

       

      I'm trying to clean phone numbers by doing two things:

      • Removing anything that's not a number from the Phone number column - no problem I used this code:

                 purgechar([Phone number], ')(-') as [Phone number]

      • Problem is, when the area code is in the Phone number column (like row 2), I want to remove it and add it to the Area code column (or into any other new field). This is where I'm stuck, I've tried various iterations of if(len, substring, purgechar and just can't get this to work.

      Any help appreciated, as always. Thanks!


      R.

       

       

       

      Area codePhone number
      14161234567
      24161234567
      3(416)1234567