使用Google表格公式将电话号码验证为特定格式 [英] Validate phonenumbers to specific format using Google Sheet formula

问题描述

我正在使用Google表格，并且希望将美国电话号码转换为以下格式:

I'm working with google sheets and would like to convert US phone numbers to the format:

1xxxyyyzzzz

例如402-333-4444应该变成14023334444

eg 402-333-4444 should be turned into 14023334444

我有一个执行此操作的应用程序脚本验证器功能:

I have an apps script validator function which does this:

var numbers = 'INVALID' 

if ( parsed_body.hasOwnProperty('PHONE') ) {


  var phone = parsed_body['PHONE'].toString();
  Logger.log(parsed_body);

  numbers = phone.replace(/[^0-9]/g, "");
  var firstChar = numbers.charAt(0);
  if ( firstChar !== '1'){ numbers = '1'+ numbers}
  Logger.log(numbers);

  if ( numbers.length !== 11){ numbers = 'NOTELEVEN'};
}

parsed_body['PHONE']=numbers;

但是我想做这张纸.这可能吗?

but I'd like to make the sheet do this. Is this possible ?

推荐答案

如果在单个公式中进行计算，则得出的公式相当长，因为与脚本版本不同，您必须重复上述步骤:

It works out to quite a long formula if you do it in a single formula, because you have to keep repeating the previous steps, unlike in the script version:

=if(len(if(left(regexreplace(""&D2,"[^0-9]",""))="1","","1")&regexreplace(""&D2,"[^0-9]",""))=11,
if(left(regexreplace(""&D2,"[^0-9]",""))="1","","1")&regexreplace(""&D2,"[^0-9]",""),"INVALID")

可以更改为数组公式:

=ArrayFormula(if(D2:D="","",if(len(if(left(regexreplace(""&D2:D,"[^0-9]",""))="1","","1")&regexreplace(""&D2:D,"[^0-9]",""))=11,
if(left(regexreplace(""&D2:D,"[^0-9]",""))="1","","1")&regexreplace(""&D2:D,"[^0-9]",""),"INVALID")))

这篇关于使用Google表格公式将电话号码验证为特定格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！