Google Sheet custom function not returning string

Question

I am new to Google Sheet scripting.

I am writing a code to strip the sixth components from a long text that is based on a naming convention. The text have 6 parts all separated by an underscore. However, my code is not returning anything

  function RetailerStrip(account) {
  var count = 0;
  var retname = "";
  var retcount = 0;
  for(var i = 0, len = account.length; i < len; i++) {
        if (account[i] =="_") {
            ++count;
            }
        if (count == 5) {
            retname[retcount]= account[i];
            ++retcount;
            }
    }
  return retname;
}

I then call this function from sheet as below

=RetailerStrip("abc_def_ghi_jkl_mno_pqr")

When I tried to declare 'retname' as an array the function did return the required text (fifth component) but the text was spread across multiple cells with on character in each cell, and not as a single string in one cell

var retname = [];

Please help

ADW · Accepted Answer · 2020-04-07 04:52:38Z

4

You could try this:

function RetailerStrip(str) { return str.split('_')[5]; }

The split() method creates an array.

But if you prefer to stick with the string-iteration method, you could use this:

function RetailerStrip(account) {
  var count = 0;
  var retname = []; // Array
  var retcount = 0;
  for (var i = 0, len = account.length; i < len; i++) {
    if (account[i] =="_") {
      ++count;
    }
    if (count == 4) {
      retname[retcount]= account[i];
      ++retcount;
    }
  }
  retname.shift(); // To get rid of the underscore from the array
  var retnameString = retname.join(''); // To convert the array to a string
  return retnameString;
}

edited Apr 7, 2020 at 4:52

answered Apr 6, 2020 at 11:53

ADW

4,2471 gold badge17 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Google Sheet custom function not returning string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related