1

Very new to this

cell data looks like:

colx
aa, bbb
oue, hed, ono
hud

need it to become:

colx              coly     colz
aa, bbb           aa       bbb
oue, hed, ono     oue      hed, ono
hud               hud

I am needing to split a column on its first delimiter then post back the first part (everything before the first delimiter)in the next column to the right of the original column and post back the rest of the string (everything after the first delimiter) to the 2nd column to the right of the original column

I found the function SplitTheString here: How do I split a string, breaking at a particular character?

I know my code does not work cant get how to fix it, in particular how to output the data

Thanks

function splitColumn() {
 var range =getColRng("Elements","colx", 2)
 var values = range.getValues());

  for (var row in values[0]) {
      values[row][0] == SplitTheString(ResultStr);) 

 }
 range.setValues(values);
 }

Helper Functions:

 function SplitTheString(ResultStr) {
     if (ResultStr != null) {
         var SplitChars = ', ';
         if (ResultStr.indexOf(SplitChars) >= 0) {
            var DtlStr = ResultStr.split(SplitChars);
            var name  = DtlStr[0];
            var street = DtlStr[1];
        }
    }
}

function getColRng(shtName, cheader,startrow) {
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(shtName);
 var lc = sheet.getMaxColumns();
 var lr = sheet.getLastRow();
 var hn = HTN(shtName,cheader)
 return sheet.getRange(startrow, hn, lr);
}
xyz
  • 2,253
  • 10
  • 46
  • 68

1 Answers1

1

How about the following sample script? This script supposes that there are data in the column A.

Sample script :

function splitColumn() {
  var ss = SpreadsheetApp.getActiveSheet()
  var range = ss.getRange(1, 1, ss.getLastRow(), 1);
  var values = range.getValues();
  var result = [];
  for (var i in values) {
    if (i == 0) {
      result.push([values[i][0], "coly", "colz"]);
    } else {
      var splitted = values[i][0].split(",");
      var others = splitted.slice(1, splitted.length).join(",");
      result.push([values[i][0], splitted[0], others]);
    }
  }
  ss.getRange(ss.getLastRow()+1, 1, result.length, result[0].length).setValues(result);
}

Input :

enter image description here

Output :

enter image description here

If I misunderstand your question, please tell me. I would like to modify.

Edit :

function splitColumn() {
  var ss = SpreadsheetApp.getActiveSheet();
  var range = ss.getRange(2, 1, ss.getLastRow(), 1);
  var values = range.getValues();
  var result = [];
  for (var i in values) {
    if (values[i][0]) {
      var splitted = values[i][0].split(",");
      var others = splitted.slice(1, splitted.length).join(",");
      result.push([splitted[0], others]);
    } else {
      result.push(["",""]);
    }
  }
  ss.getRange(2, 2, result.length, result[0].length).setValues(result);
}
Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Tanaike, thank you. This is what I am after, but when I run this noting happens, the code runs but no splitting occurs here is google sheet https://docs.google.com/spreadsheets/d/1GPO8Rgzrc9BNWYmeklyEojl5GyedGIxg-gMlV1sWH28/edit?usp=sharing – xyz Jan 05 '18 at 01:42
  • @Tim I'm sorry for the inconvenience. And thank you for your sample spreadsheet. By your shared spreadsheet, I updated my answer. Please confirm it. – Tanaike Jan 05 '18 at 01:56
  • Tanaike, it works perfectly!!. THANK YOU, I greatly appreciate this – xyz Jan 05 '18 at 03:47
  • @Tim Welcome. I'm glad your problem was solved. Thank you, too. – Tanaike Jan 05 '18 at 11:58