Created
October 26, 2012 11:33
-
-
Save davemac/3958298 to your computer and use it in GitHub Desktop.
Google Apps Script to split cells
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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