Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Formatting field with inconsistent phone number format

Topic Labels: Formulas
Solved
Jump to Solution
694 1
cancel
Showing results for 
Search instead for 
Did you mean: 

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!

1 Solution

Accepted Solutions

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.

See Solution in Thread

1 Reply 1

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.