Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Re: Standard formatting for phone numbers

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Solved

Jump to Solution

0
4303
0

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

Reply

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

Reply

9 Replies 9

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

- The little-known ‘rimshot’ emoji. :winking_face:

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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é

Reply

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 24, 2019 12:07 AM

I see your point :slightly_smiling_face:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
```

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 16, 2023 04:32 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 16, 2023 04:45 PM - edited Mar 16, 2023 04:46 PM

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 11, 2023 11:15 AM - edited May 11, 2023 11:17 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.