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.

Standard formatting for phone numbers

Topic Labels: Formulas
4202 5
cancel
Showing results for 
Search instead for 
Did you mean: 

My phone number field has rows with different formatting.

e.g.
12824673737

and
(510) 890-9699

How can I force all the phone numbers to be of one format: namely, a 10 digit number without any punctuation in between

5 Replies 5

Edit: See my revised, far-simpler formula in the third reply to this post. The only difference is the formula in this reply returns a text string while the other formula returns a number value. (If you use the other formula — which is based on an Airtable behavior I really wish they would fix — be sure to format the field as an integer .)


Enter them that way! :drum: ¹

You don’t say what sort of field {Phone} is; I’m assuming it’s a phone number field, because a 10-digit number entered into a phone number field will be formatted as '(NPA) NXX-####', while an 11-digit number — such as a phone number with a preceding '1', is formatted as, well, an 11-digit number.

You can’t enforce a certain format for data entry; however, you can create a formula field (called something like {CleanPhone} that forces all phone numbers into your preferred format. The following formula strips phone numbers of '(', ')', '-', and ' ', and it strips off leading '1's:

SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(
                IF(
                    LEFT(
                        Phone,
                        1
                        )='1',
                    RIGHT(
                        Phone,
                        LEN(
                            Phone
                            )-1
                        ),
                    Phone
                    ),
                '(',
                ''
                ),
            ')',
            ''
            ),
        '-',
        ''
        ),
    ' ',
    ''
    )

You can copy-and-paste that into the formula field (including line breaks and indentation). If your phone number field is named anything other than {Phone}, adjust accordingly.


  1. The little-known ‘rimshot’ emoji. :winking_face:

@W_Vann_Hall, when using a formula, would the Concatenate option work too? Something like this:
CONCATENATE("(",LEFT(PHONE,3),") “,MID(PHONE,4,3),”-",RIGHT(PHONE,4))

Regards,
André

Yes, that would work to add punctuation to an unformatted phone number (assuming the user wanted US-style phone punctuation). Personally, I’d do it using the '&' form of concatenation, simply because I always get confused parsing an explicit CONCATENATE() function — but I know people (many of whom spreadsheet programming wizards) who are the complete opposite, and can’t stand all those ampersands. :winking_face: (You’d still need to strip a leading '1', if it existed.)

The OP, though, wanted to strip punctuation from a punctuated number — or, at least, I think he did. (…) Yes, I checked: He wanted an unpunctuated 10-digit number. That’s why I sent him through the nested SUBSTITUTE()s, peeling back the number.

There’s actually a much easier way to convert phone fields to a consistent 10-digit number — one I didn’t think of at first, because it takes advantage of an Airtable behavior I strongly believe in urgent need of correction: Namely, the way VALUE() [usually] merely ignores non-numeric characters, rather than returning an error. So

MOD(
    VALUE(
        Phone
        ),
    10000000000
    )

will also convert a column of mixed formatted 10-digit phone numbers and unformatted 11-digit numbers into a consistent column of unformatted 10-digit numbers.

I see your point :slightly_smiling_face:

My code looks something like this:

IF(LEN(Contact_Phone_Mobile)<5,"", IF(LEFT(MOD(
    VALUE(
        Contact_Phone_Mobile
        ),
    100000000000
    )&"",1)=1,"+"&MOD(
    VALUE(
        Contact_Phone_Mobile
        ),
    100000000000
    )&"","+1"&MOD(
    VALUE(
        Contact_Phone_Mobile
        ),
    100000000000
    )&""))```

The "" are to convert it into a string. Formula errors if it is not a string.