Extract the digits and append it in a different cell?

Question

I am trying to automatically RegExp(extract) the digits(AREA number) in Column 3 combined with the Text 'A' to append in Column 1 Date INDEX.

The problem is I'm not yet familiar in using google sheets app-scripts. Tried looking for solutions with similar situation as me, but to no avail. I don't know to put VBA to app-scripts.

Tried using some codes. I still can't seem to make it work.

Can anyone point me in the right direction?

Thank you if you can help me out. Thanks.

EDIT: The scenarios is in the office i cant make column for the formula. It must be "behind the scene".

My googlesheets

//NOT WORKING code

function onEdit(e) {
  var rg=e.range;
  var sh=e.range.getSheet();
  var area=sh.getName();
  var regExp = new RegExp("\d*");  // Extract the digits
  var dataIndex = regExp.exec(area)[1];
  if(rg.columnStart==3) {  // Observe column 3
    var vA=rg.getValues();
    for(var i=0;i

Tedinoz · Accepted Answer

This answer extends your approach of using a script with an OnEdit trigger. But there are a number of differences between the two sets of code.

The most significant difference is that I have used the Javascript split method (var fields = value.split(' ');) to get distinct values from the data entry.

Most of the other differences are error checking:

if(rg.columnStart === 3 && area === "work") {: test for sheet="work" as well as an edit on Column C
var value = e.value.toUpperCase();: anticipate that the test might be in lower case.
if (fields.length !=2){: test that there are two elements in the data entry.
if (fields[0] != "AREA"){: test that the first elment of the entry is the word 'area'
if (num !=0 && numtype ==="number"){; test that the second element is a number, and that it is NOT zero.
if (colA.length !=0){: test that Column A is not empty
var newColA = colA+"A"+num;: construct the new value for Column A by using unary operator '+'.

function onEdit(e){

  // so5911459101

  // test for edit in column C and sheet = work

  var ss = SpreadsheetApp.getActiveSpreadsheet;

  // get Event Objects
  var rg=e.range;
  var sh=e.range.getSheet();
  var area=sh.getName();
  var row = rg.getRow();

  // test if the edit is in Column C of sheet = work
  if(rg.columnStart === 3 && area === "work") {  // Observe column 3 and sheet = work
    //Logger.log("DEBUG: the edit is in Column C of 'Work'")

    // get the edited value
    var value = e.value.toUpperCase();
    //Logger.log("DEBUG: the value = "+value+", length = "+value.length+", uppercase = "+value.toUpperCase());

    // use Javascript split on the value
    var fields = value.split(' ');
    //Logger.log(fields);//DEBUG
    // Logger.log("DEBUG: number of fields = "+fields.length)

    // test if there are two fields in the value
    if (fields.length !=2){
      // Logger.log("DEBUG: the value doesn't have two fields")
    }
    else{
      // Logger.log("DEBUG: the value has two fields")

      // test if the first field = 'AREA'
      if (fields[0] != "AREA"){
        // Logger.log("DEBUG: do nothing because the value doesn't include area")

      }
      else{
        // Logger.log("DEBUG: do something because the value does include area")

        // get the second field - it should be a value
        var num = fields[1];
        num =+num
        var numtype = typeof num;
        // Logger.log("DEBUG: num= "+num+" type = "+numtype); //number


        // test type of second field
        if (num !=0 && numtype ==="number"){
          // Logger.log("DEBUG: the second field IS a number")

          // get the range for the cell in Column A
          var colARange = sh.getRange(row,1);
          // Logger.log("DEBUG: the ColA range = "+colARange.getA1Notation());

          // get the value of Column A
          var colA = colARange.getValue();
          // Logger.log("DEBUG: Col A = "+colA+", length = "+colA.length);

          // test if Column A is empty
          if (colA.length !=0){

            var newColA = colA+"A"+num;
            // Logger.log("DEBUG: the new cola = "+newColA);

            // update the value in Column A
            colARange.setValue(newColA);

          }
          else{

            // Logger.log("DEBUG: do nothing because column A is empty")

          }

        }
        else{
          // Logger.log("DEBUG: the second field isn't a number")
        }

      }

    }

  } 
  else{
    //Logger.log("DEBUG: the edit is NOT in Column C of 'Work'")

  }

}

REVISION

If the value in Column C is sourced from data validation, then no need for and testing except that the edit was in Column C and the sheet = "work".

Included two additional lines of code:

var colAfields = colA.split('-');

var colAdate = colAfields[0];

This has the effect of excluding any existing characters after the hyphen, and re-establishing the hyphen, row number plus "A" and the AREA numeral.

function onEdit(e){

  // so5911459101 revised
  // only one test - check for ColumnC and sheet="work"

  // test for edit in column C and sheet = work

  var ss = SpreadsheetApp.getActiveSpreadsheet;

  // get Event Objects
  var rg=e.range;
  var sh=e.range.getSheet();
  var area=sh.getName();
  var row = rg.getRow();

  // test if the edit is in Column C of sheet = work
  if(rg.columnStart === 3 && area === "work") {  // Observe column 3 and sheet = work
    Logger.log("DEBUG: the edit is in Column C of 'Work'")

    // get the edited value
    var value = e.value
    //Logger.log("DEBUG: the value = "+value+", length = "+value.length);

    // use Javascript split on the value
    var fields = value.split(' ');
    //Logger.log(fields);//DEBUG

    // get the second field - it should be a value
    var num = fields[1];

    // get the range for the cell in Column A
    var colARange = sh.getRange(row,1);
    // Logger.log("DEBUG: the ColA range = "+colARange.getA1Notation());

    // get the value of Column A
    var colA = colARange.getValue();
    // Logger.log("DEBUG: Col A = "+colA+", length = "+colA.length);

    // use Javascript split on Column A in case of existing value
    var colAfields = colA.split('-');
    var colAdate = colAfields[0];

    // build new value
    var newColA = colAdate+"-"+row+"A"+num;
    // Logger.log("DEBUG: the new cola = "+newColA);

    // update the value in Column A
    colARange.setValue(newColA);

  } 
  else{
    Logger.log("DEBUG: the edit is NOT in Column C of 'Work'")

  }

}

Extract the digits and append it in a different cell?

Answers (1)

Related Questions