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! ¹

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:
   

SUBSTITUTE(

    SUBSTITUTE(

        SUBSTITUTE(

            SUBSTITUTE(

                IF(

                    LEFT(

                        Phone,

                        1

                        )='1',

                    RIGHT(

                        Phone,

                        LEN(

                            Phone

                            )-1

                        ),

                    Phone

                    ),

                '(',

                ''

                ),

            ')',

            ''

            ),

        '-',

        ''

        ),

    ' ',

    ''

    )

@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() wusually] 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.

MOD(

    VALUE(

        Phone

        ),

    10000000000

    )

I see your point

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.

Have you found a solution to this problem? I am thinking of getting a phone number and using it for all operational purposes.

Hey there! I totally understand the need for a dedicated phone number for operational purposes. It can make things so much easier to keep track of. Have you considered using a phone number generator with SMS capabilities? I actually use one for work and it's been a game-changer. No more mixing up personal and work calls or texts! Let me know if you have any questions or want more info. Good luck with finding a solution!

My advice to you, it's better to leave these ordinary numbers, since they are no longer relevant. Having a VoIP number may be better than having a regular phone number that has one format, for example, a 10-digit number with no punctuation marks between them. With the help of VoIP technologies, you can choose a number with a format that is convenient for you, which may contain additional punctuation marks, spaces or even letters. This can be useful for branding your business and creating memorable numbers.

Hi Salim,

Actually I've had the same problem which @Richard11 has, but I've solved it by getting some information in this website: Phone Number Generator in the blog section they've mentioned about some formulas for solving these type of numbers.

I’ve dealt with the same issue before, and standardizing phone numbers can be frustrating, especially when formats vary. One approach is to use a simple script or an Excel formula to strip out any non-numeric characters.

Also, if you’re working with phone numbers for verifications or registrations, using a temporary number can be a great option. I’ve found services that let you receive SMS online UK really helpful in situations where you need a clean, standardized number format without formatting inconsistencies. Definitely worth checking out!