Skip to content

Instantly share code, notes, and snippets.

@davemac
Created October 26, 2012 11:33
Show Gist options
  • Save davemac/3958298 to your computer and use it in GitHub Desktop.
Save davemac/3958298 to your computer and use it in GitHub Desktop.
Google Apps Script to split cells
function myFunction(){
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
//get the names from column B
var n = sheet.getRange('B:B').getValues();
var f = [];
for(var i=0; i<n.length; i++){
//Reformat each name and push it in array f
f.push(reformat(n[i][0]));
}
//Write back the reformatted name in Column C & D
sheet.getRange('C:D').setValues(f);
}
function reformat(e){
//regex to match '&'
var r1 = /[&]/;
//regex to match space
var r2 = /[\s]/;
//if cell is empty, return empty
if (e == ''){
return ['',''];
}
//if the cell contains '&', return first name and last name with no & sign
else if(r1.test(e)){
var k = e.split(/\s(?=\S*$)/);
return [k[0].replace(r1,''),k[1]];
}
//If it contains space only, split it to first and last name
else if(r2.test(e)){
return e.split(r2);
}
//if only first name, return first name
else {
return [e,''];
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment