Spreadsheet Custom Function Split

Question:

( by Michael Horwitz )

I used the split Function. The names are in column A and are separated by a space so I did

=split(a1," ") which worked fine except some of the names have middle initials.

So the function split John A. Doe into first and middle initial initial instead of first and last names.

Is there a way to tell the function to ignore the first space so the middle initial would be included in the first name?

Alternatively to delete the middle initial entirely and only return the first and last name?

Thank you

Solution:

Have a look at the following screenshot:

I have the following formula in Cell B1:

=ksplit(A1:A4)

ksplit() is the custom function developed in Script, which you can directly use in your spreadsheet.

Have a look at the following code:

///////////////////////////////////////

function ksplit() {
  var values = arguments[0];
  var s,returnArray = [];
  if( values.constructor == Array ) {
    for(var i=0;i<values.length;i++) {
      s = values[i].toString().split(" ");
      if(s.length>1) returnArray.push([s[0],s[s.length-1]]);
      else returnArray.push([s[0]]);
    }
    return returnArray;
  }
  else {
    s = values.split(" ");
    if(s.length>1) return [s[0],s[s.length-1]];
    else return [s[0]];
  }
}

///////////////////////////////////////

put the above code in Script editor of your Spreadsheet.

The custom function ksplit() will split the argument with delimiter " ", that is a space. And after splitting, it will return the first and last name. 


And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive