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!