Convert text format to a value format

user_17438392 0
Viewed 52

I am looking to create a script that will automatically convert a phone number that is cut & pasted into Google Sheets. The phone number is in a text format and I need to change it into a value. Currently, I have to have an additional column(s) for each pasted phone number that converts it. I would like to eliminate having these extra columns.

The formula I am using to do the converting are:

Column C:

=IF(ISBLANK(A3)," ",(VALUE(REGEXREPLACE(A3, "[-]", ""))))

Column D:

=IF(ISBLANK(B3)," ",(VALUE(REGEXREPLACE(B3, "[-]", ""))))

I'm very new to scripts and just starting to learn about it. Any help would be much appreciated. Thank you.

Sheets Screenshot

Shared Link

google-sheets
1 Answers

I think what you are looking for is the onEdit-trigger. There is quite a good video on youtube explaining the basics, as well as the general documentation for the onEdit trigger.

I'm no Google AppScript expert, so all I can give you is a sample script that work for Cell A3. I leave it up to you to complete the code and build something that will work with all your cells. ;) The code should be self explaining - especially after watching the linked video above. Most of the code is basic Javascript, so it should be simple to understand.

// source code example for cell A3
function onEdit(e) {
  var range = e.range;
  var spreadSheet = e.source;
  var sheetName = spreadSheet.getActiveSheet().getName();
  var column = range.getColumn();
  var row = range.getRow();
  var inputValue = e.value;
  
  // column 1 is "A", so column 1, row 3 is Cell "A3"
  if (sheetName == "Sheet1" && column == 1 && row == 3) {
    var number = inputValue.replace(/[-]/, '');
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("A3").setValue([number]);
  }
}
user_94671590
Related