Jun 22, 2019 06:29 PM
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! Go to Solution.
Jun 22, 2019 11:17 PM
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.
Jun 22, 2019 07:39 PM
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.
Jun 22, 2019 10:47 PM
@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é
Jun 22, 2019 11:17 PM
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.
Jun 24, 2019 12:07 AM
I see your point :slightly_smiling_face:
Aug 03, 2021 03:37 PM
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.
Mar 16, 2023 04:32 PM
Have you found a solution to this problem? I am thinking of getting a phone number and using it for all operational purposes.
Mar 16, 2023 04:45 PM - edited Mar 16, 2023 04:46 PM
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!
May 11, 2023 11:15 AM - edited May 11, 2023 11:17 AM
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.
May 22, 2024 10:26 PM
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.