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
Solved
Standard formatting for phone numbers
+1Best answer by W_Vann_Hall
@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.
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.
- The little-known ‘rimshot’ emoji. :winking_face:
+18Edit: 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.
- 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é
@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.
+18Yes, 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.
Have you found a solution to this problem? I am thinking of getting a phone number and using it for all operational purposes.
+2Hey 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.
+1Hey 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!
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.
+1I’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!
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.