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

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 table

let table = base.getTable("Airdrop");



// get the records from this table

let records = await table.selectRecordsAsync();



// Update the records

for (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

}