Formatting field with inconsistent phone number format

[Michelle_Sokol]

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!

[Michelle_Sokol · Accepted solution]

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.