Use This Script to Separate the First and Last names in Google Sheets
source link: https://hackernoon.com/use-this-script-to-separate-the-first-and-last-names-in-google-sheets
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
@kcl
Khadka's Coding Lounge.As a freelancer, I create addons and scripts to automate ...
Receive Stories from @kcl
Credibility
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:
-
Create a custom menu in your spreadsheets tabs with the title Custom Menu.
-
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.
-
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 on: https://linkedin.com/in/nibesh-khadka https://nibeshkhadka.com [email protected] */
How To Add Apps Script Code To a Spreadsheet
If you don't know how to add this script to your sheet, then just click the Extensions tab and then Apps Script as shown in the image below.
Open Script Editor
Now, similar to the previous blogs, you can now just:
- Save the code.
- Reload the document. Where you'll see the custom menu as shown below
- 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 Splitting Names
Thank You for Your Time
Don’t forget to like and share this blog.
Also published here.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK