Standard formatting for phone numbers

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

1 Like

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. :wink:
1 Like

@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é

1 Like

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. :wink: (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.

3 Likes

I see your point :slight_smile: