在工作表单元格中格式化电话号码 [英] Formatting phone number in place in sheet cell

问题描述

我正在使用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;

我也问了这个问题使用以下方法将电话号码验证为特定格式Google Sheet公式，显然，它可以作为相邻列中的公式来完成.但是，在我的情况下，每一行都是通过从提交的表单到Google表格的发帖请求中创建的，我想将就地"电话号码(即单元格中的电话号码)的格式设置为1aaabbbcccc.这可能吗?

I also asked this question Validate phonenumbers to specific format using Google Sheet formula and obviously it can be done as a formula in an adjacent column. However in my case each row is created by a post request to google sheets from a submitted form and I'd like to format the phone number IN PLACE (ie in the cell) to look like 1aaabbbcccc. Is this possible?

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

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

推荐答案

• 对于Google Spreadsheet，您想通过添加1将402-333-4444转换为14023334444.
• 您想使用Google Apps脚本实现这一目标.
• For Google Spreadsheet, you want to convert 402-333-4444 to 14023334444 by adding 1.
• You want to achieve this using Google Apps Script.

为了实现您的目标，我建议将转换后的值覆盖到单元格中.

In order to achieve your goal, I would like to propose to overwrite the converted values to the cells.

在此示例脚本中，它假定将值放入单元格"D2:D".运行脚本时，单元格"A2:A"将被转换后的值覆盖.

In this sample script, it supposes that the values are put to the cells "D2:D". When you run the script, the cells "A2:A" is overwritten by the converted values.

function myFunction() {
  const sheetName = "Sheet1"; // Please set the sheet name.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getRange(2, 4, sheet.getLastRow() - 1, 1);  // Column "D"
  const values = range.getValues();
  const converted = values.map(([v]) => {
    if (/\d{3}-\d{3}-\d{4}/.test(v)) {
      let temp = v.replace(/-/g, "");
      console.log(v.charAt(0))
      const tempV = v.charAt(0) != 1 ? 1 + temp : temp;
      return [tempV.length == 11 ? tempV : v];
    }
    return [v];
  });
  range.setValues(converted);
}

注意:

• 当您还要将402 333 4444转换为14023334444时，请进行以下修改.

  Note:

  • When you want to also convert 402 333 4444 to 14023334444, please modify as follows.

    • 来自

    • From

    if (/\d{3}-\d{3}-\d{4}/.test(v)) {
      let temp = v.replace(/-/g, "");
    

  • 收件人

  • To

    if (/\d{3}-\d{3}-\d{4}|\d{3} \d{3} \d{4}/.test(v)) {
      let temp = v.replace(/-| /g, "");
    

  这篇关于在工作表单元格中格式化电话号码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！