Separating area code from phone number into new field
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.