Help

Standard formatting for phone numbers

Topic Labels: Formulas
Solved
Jump to Solution
10496 9
cancel
Showing results for 
Search instead for 
Did you mean: 
Richard
5 - Automation Enthusiast
5 - Automation Enthusiast

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 Solution

Accepted Solutions

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.

See Solution in Thread

9 Replies 9

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:

Aloft_Orders
4 - Data Explorer
4 - Data Explorer

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.
IsabelaTany
4 - Data Explorer
4 - Data Explorer

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

SalimSalim
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

Alex_Backer
4 - Data Explorer
4 - Data Explorer

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 @Richard 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.