Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Formatting field with inconsistent phone number format

Topic Labels: Formulas
Solved
Jump to Solution
1957 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Michelle_Sokol
4 - Data Explorer
4 - Data Explorer

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
Michelle_Sokol
4 - Data Explorer
4 - Data Explorer

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
Michelle_Sokol
4 - Data Explorer
4 - Data Explorer

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.