DEV Community

Cover image for Use This Script to Separate The First and Last Names in Spreadsheets?
Nibesh Khadka
Nibesh Khadka

Posted on • Updated on • Originally published at kcl.hashnode.dev

Use This Script to Separate The First and Last Names in Spreadsheets?

Would you like to separate the column in your spreadsheet that consists of the full names of people into first and last names? Well then stick this very simple script to your spreadsheets apps script editor and you'll be able to do just that.

Split The Selected Column

The following bound script will do three things:

  1. Create a custom menu in your spreadsheets tabs with the title Custom Menu.

  2. After you call select the custom menu, It will check the cell you've selected to split into. The script won't work if you don't select a cell in the right column.

  3. Separate the whole column into two columns, with the first value in the first column and the second(& rest if there are any) in the new column.

function splitName() {
  // get sheet and data
  const sheet = SpreadsheetApp.getActiveSheet();
  // get selected row
  const activeColIndex = sheet.getActiveRange().getColumn();

  const data = sheet.getDataRange().getValues();
  const lastRow = data.length;

  sheet.getRange(1,activeColIndex,lastRow,1).splitTextToColumns();

}

// if you're new and only want this feature the use this code
/**
 * OnOpen trigger that creates menu
 * @param {Dictionary} e
 */
function onOpen(e) {
  createCustomMenu();
}

/**
 * Menu creates menu UI in spreadsheet.
 */
function createCustomMenu() {
  let menu = SpreadsheetApp.getUi().createMenu("Custom Menu"); // Or DocumentApp or SlidesApp or FormApp.

  menu.addItem("Split Names", "splitName");
  menu.addToUi();
}

/**
 * Code By Nibesh Khadka.
 * I am freelance and Google Workspace Automation Expert.
 * You can find me in:
 * https://linkedin.com/in/nibesh-khadka
 * https://nibeshkhadka.com
 * me@nibeshkhadka.com
 */

Enter fullscreen mode Exit fullscreen mode

How To Add Apps Script Code To a Spreadsheet?

If you don't know how to add this script to your sheet then, then just click the Extensions tab and then Apps Script as shown in the image below.

Open Scrpit Editor

Now, similar to the previous blogs, you can now just:

  1. Save the code.

  2. Reload the document. Where you'll see the custom menu as shown below

  3. And execute the function.

Executing The Function

Here are a few images to guide what the operation will look like in your docs.

Custom Menu

Before and After Name Split

Thank You for Your Time

My name is Nibesh Khadka, and as a freelance automation expert, I specialize in automating Google products with Apps Script. So let's get started! If you need my services let me know.

Don’t forget to like and share this blog.

Like, Share and Follow Khadka's Coding Lounge

Top comments (0)