Skip to main content

I’m pretty new to Airtable & formulas. Was hoping someone could help me find a solution to cleaning up data for a phone field.



I’m pulling data from multiple sources and while most numbers are formatted consistently, there are a few outliers I want to be sure to account for.



Most common format: Dirct: XXX-XXX-XXXX


A decent number also have an extension on the end in the format of " ext. X" , which are always one character.


Some other formats that throw things off:


There are just a few that are entered as (XXX) XXX-XXXX


A handful of others that are Cell: XXX-XXX-XXXX


And a small percentage that include multiple phone numbers as Dirct: XXX-XXX-XXXX, Dirct: XXX-XXX-XXXX



My goal: If there are two phone numbers listed, I’d like to move the second one into a new column for an additional phone field. My guess is to parse this by comma somehow, but again – new to Airtable and having trouble figuring this out!



I’d also like to remove the cell: or dirct: in front of all numbers. They can remain in any format beyond that (dashes & parentheses are OK, but a plain number would also be fine).



The extensions can be removed, but I figure this can be done in the final step by limiting the character count.



Thank you, Airtable community!

I found an easy solution that should work. Wanted to update this thread in case anyone else finds it helpful.



I just removed all non-numeric characters manually as the VALUE() function was giving me an NaN error:



SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({Phone Number},“ext. “,””),"(",""),")",""),“Dirct: “,””),“Cell: “,””)



And then a second formula field to only keep the first 10 characters to make sure an extension didn’t get stuck in the field: LEFT({Phone Number Formula)}&"",10) - not sure why I needed to add the &"" in there, but that’s the only way it would work.

Reply


Terms & Conditions