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

[Cameron_Goldber]

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 solution]

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 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
}

[Cameron_Goldber]

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!

[QuantumStep]

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.