Format Phone Numbers in Sheets using Google App Script from 1xxxxxxxxxx to xxx-xxx-xxxx

user_1839949 0
Viewed 535

I am trying to modify the following Google App Script to convert phone numbers from

11-digits to 10 digits with hyphens. So the script would need to remove the leading 1 and add the hyphens.

For example: 19734776262 to 973-477-6262

The following script does the opposite. Found on Formatting phone number in place in sheet cell

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);
}
array-formulas google-sheets-formula google-apps-script google-sheets regex
4 Answers

If a formula solution works for you here is my approach, otherwise excuse me for the misunderstanding:

=CONCATENATE(left(right(A1,len(A1)-1),3),"-",mid(right(A1,len(A1)-1),4,3),"-",right(A1,4))

example

user_112252910

As Marios also mentioned, "If a formula solution works for you here is my approach"

=REGEXEXTRACT(TEXT(I1,"0-000-000-0000"),"-(.*)")

To apply the above formula to a range of cells, please use:

=ArrayFormula(IF(I1:I="",,REGEXEXTRACT(TEXT(I1:I,"0-000-000-0000"),"-(.*)")))

(Please adjust ranges to your needs)

enter image description here

Functions used:

user_15277800

Using regex:

=RIGHT(REGEXREPLACE(A2,"(?:^1)?(\d{3})","-$1"),12)

  • REGEXREPLACE the following pattern

    • (?:^1)? - A leading 1 (optional)- non capturing group
    • \d{3} - exactly 3 occurrences of a digit
    • (...) - capture group $1

  • replace with -$1 - leading hyphen - and captured group

  • RIGHT to get exactly 12 right most characters

user_84044530

If you really want a script and not a formula, this script will do it. (I modified the one you provided.)

function myFunction() 
{
  const sheetName = "Sheet1"; // Please set the sheet name.
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getRange('D:D');  // Column "D"
  const values = range.getValues();
  const converted = values.map(([v]) => {
    if (/^\d{11}$/.test(v))
    {
      let sv = v.toString();
      return [[sv.substr(1, 3), sv.substr(4, 3), sv.substr(7)].join('-')];
    }
    return [v];
  });
    
  range.setValues(converted);
}

It just takes the substring chunks from the input and joins them together.

user_42943990
Related