Skip to main content

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



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()S0] === "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


}


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()S0] === "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!

You can set up a regex pattern rule in the DataGuard extension to prevent users from entering an incorrect telephone format and keep your data clean.

Reply


Terms & Conditions