Data validation with phone numbers. Adding north american area code automatically?

Question

Hey Community!
Here’s my problem: I have users adding phone numbers to records. Problem is, the way the are inputting records doesn’t allow me to have Data validation. I need the number they input in a specific format, and I was hoping there might be a formula that could do this.
Format we need: 1 (XXX) XXX-XXXX
Inputs we might get (because users make mistakes) =

1 xxx xxx xxxx
xxx xxx xxxx
xxxxxxxxxx
1 (xxx) xxx xxxx
(xxx) xxx xxxx

What is important for us is there is the area code, aka, a “1” at the front of every number. I was thinking about creating a formula that adds a “1” at the start, but in the circumstances where a user does what we want, and adds a “1”, I don’t want to add it as it’d break it.
Does anyone know how to put a formula together that’ll do this?
PS,
Bonus points if you can ensure it looks “pretty” by formatting it in this way: 1 (XXX) XXX-XXXX

Bill_French · Accepted Answer

Cameron_Goldberg:I need the number they input in a specific format, and I was hoping there might be a formula that could do this.Right, there isn’t unfortunately and if you try to build a formula-only approach, it gets pretty ugly and convoluted. I think I needed three fields to achieve it when I tried it a few years ago. Perhaps there’s a formula geek amongst us that can whip one up.COMMENTARY: Airtable should make it possible to perform data validation by supporting an option trigger to call into a script block and return the proper format or an error popup…To get around this, I help clients perform a lot of data cleansing using the new script automation and script block features. Like this …Script:/*   ***********************************************************   ScriptBloqs - Phone Number Validation Logic   Copyright (c) 2020 by Global Technologies Corporation   ALL RIGHTS RESERVED   *********************************************************** */output.markdown('# Formatting Phone Numbers');// get the name of the tablelet table = base.getTable("Airdrop");// get the records from this tablelet records = await table.selectRecordsAsync();// Update the recordsfor (let record of records.records) {    if (record.getCellValue("Phone Number"))    {        //        // apply the format        //        let thisValue = record.getCellValue("Phone Number");        // output.inspect(thisValue);        // remove any + signs if they exist        thisValue = thisValue.replace("+", "");        // if the first character is a one, do nothing, otherwise append one (this is north america after all)        thisValue = (thisValue.toString()[0] === "1") ? thisValue : "1 " + thisValue;        // regex the number        thisValue = formatPhoneNumber(thisValue);        // per the client's request, eliminate the + sign that the regex added        thisValue = thisValue.replace("+1 ", "1 ");        await table.updateRecordAsync(record, {            "Phone Number Formatted" : thisValue        });        }}//// format phone number//function formatPhoneNumber(phoneNumberString) {  var cleaned = ('' + phoneNumberString).replace(/\D/g, '')  var match = cleaned.match(/^(1|)?(\d{3})(\d{3})(\d{4})$/)  if (match) {    var intlCode = (match[1] ? '+1 ' : '')    return [intlCode, '(', match[2], ') ', match[3], '-', match[4]].join('')  }  return null}

Cameron_Goldber · Answer

Cameron_Goldberg:I need the number they input in a specific format, and I was hoping there might be a formula that could do this.Right, there isn’t unfortunately and if you try to build a formula-only approach, it gets pretty ugly and convoluted. I think I needed three fields to achieve it when I tried it a few years ago. Perhaps there’s a formula geek amongst us that can whip one up.COMMENTARY: Airtable should make it possible to perform data validation by supporting an option trigger to call into a script block and return the proper format or an error popup…To get around this, I help clients perform a lot of data cleansing using the new script automation and script block features. Like this …Script:/*   ***********************************************************   ScriptBloqs - Phone Number Validation Logic   Copyright (c) 2020 by Global Technologies Corporation   ALL RIGHTS RESERVED   *********************************************************** */output.markdown('# Formatting Phone Numbers');// get the name of the tablelet table = base.getTable("Airdrop");// get the records from this tablelet records = await table.selectRecordsAsync();// Update the recordsfor (let record of records.records) {    if (record.getCellValue("Phone Number"))    {        //        // apply the format        //        let thisValue = record.getCellValue("Phone Number");        // output.inspect(thisValue);        // remove any + signs if they exist        thisValue = thisValue.replace("+", "");        // if the first character is a one, do nothing, otherwise append one (this is north america after all)        thisValue = (thisValue.toString()[0] === "1") ? thisValue : "1 " + thisValue;        // regex the number        thisValue = formatPhoneNumber(thisValue);        // per the client's request, eliminate the + sign that the regex added        thisValue = thisValue.replace("+1 ", "1 ");        await table.updateRecordAsync(record, {            "Phone Number Formatted" : thisValue        });        }}//// format phone number//function formatPhoneNumber(phoneNumberString) {  var cleaned = ('' + phoneNumberString).replace(/\D/g, '')  var match = cleaned.match(/^(1|)?(\d{3})(\d{3})(\d{4})$/)  if (match) {    var intlCode = (match[1] ? '+1 ' : '')    return [intlCode, '(', match[2], ') ', match[3], '-', match[4]].join('')  }  return null}Thank you so much for putting this together. This will be incredibly helpful for ensuring our phone number data is usable.Thank you so much for putting this together!

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded