Google Sheets – How to Separate the First Name from a Full Name Cell

left, right, find, length

Every academic quarter I receive a list of students by their full name in one cell that I need to split into a cell for the first name and then a cell for the middle and last names combined. 

This fairly simple process can be achieved with the Google Sheets formulas left, right and find. 

If you are in a hurry, here are the formulas below: 

First Names

To get the first name we do the following: 

=LEFT(A2,FIND(” “,A2))

Where ‘A2’ is the cell that we have our full name in – in our case Vasco Nunez de Balboa.

When you have multiple formulas in a cell it’s often best to go from the inside out. So let’s first look at what FIND does. 

FIND looks inside the cell for the first value that we want to search for. For us, it’s an empty space, ” “. Find then returns the numerical position of that found item.  To do this FIND takes two arguments: 

=FIND(the item we are searching for, the cell or string the item is in)

For example if we are searching for the location of the space in
 Vasco Nunez de Balboa  which is in cell A2,  we would do the following:

=FIND(” “,A2)

Which would give the result: 6

There are five letters in the first name Vasco the the space would be position 6. 

Now that we have the position  of the space, lest just grab everything in the cell to the left of that space. We do this with LEFT

LEFT also take two values. The first is cell location and the second is the number characters we want to take from the left hand side. 

LEFT(cell location, number of characters from the left)

Now that we know the first space is character 6, the formula would look like this:

LEFT(A2,6)

We then replace the 6 with our FIND formula and we are good to go.

Continue reading “Google Sheets – How to Separate the First Name from a Full Name Cell”

Google Apps Script – How to Get the Sheet Name and Spreadsheet Name and add to a Cell on Google Sheets with a Custom Function

Google Apps Script, Custom Functions

Boy, are these titles getting longer. 

But that’s pretty much the gist of it. In this post we will look at creating a Google Apps Script Custom Function that allows you to do Three thing in Google Sheets: 

  1. Get the current sheet name.  That’s the same sheet name as the cell you are working in.
  2. Get all the sheet names. A full list of all the sheet names. 
  3. Get the name of the Spreadsheet file. 
Get Sheet Names and Spreadsheet Names Only - Google Apps Script

The above picture is pretty self explanatory. If you type in:

=SHEETNAME(#)

Where “#”  is a number 0, 1 or 2 you will get the results displayed in the picture. Any other number will display an error. 

How do I add this amazing Custom Function To my Google Sheet?

First copy the code below. Then go to <Tools>>Script Editor> This will open the Google Apps Script Editor. 

Next Rename the Project to whatever you want. I usually name it the same as my sheet. 

Next delete all the code in the Code.gs file and paste in our code. 

<File><Save> in the Project and then return to your spreadsheet and give it a try. 

The Code

You can learn more about how to build a Custom Function by following my tutorial:

Application

SHEETNAME  can be used in conjunction with other built-in functions in Google Sheets. 

It can be used in part, or as a whole as a naming or titling tool. For example, imagine our Spreadsheet file title is 2018-2019 Top Stocks and our Sheet Name for the current tab is Tech. Perhaps when we duplicate this file each year we just want the title inside our Tech Sheet to automatically update:

Which would generate:

2018-2019 Top Stocks-Tech

Another options might be a referencing tool to quickly create formulas in other sheets. Take a look a this example: 

Get SheetName Example Google Apps Script

Conclusion

So, I would be interested to see what you use this custom function for, if you found it useful. 

Have you done anything interesting with the code? I would love to see how you have advanced the Google Custom Function.

~Yagi

Google Apps Script – Copy Selected Columns in One Sheet and Add Them To The Bottom of Different Selected Columns in Another

I recently had a legend (a.k.a reader) ask about how to copy selected columns from one sheet and append them to the bottom of different selected columns in another sheet. 

To me, this seemed like a pretty common task that one might face, so in response I decided to create a template function to easily do this and reuse on the same project or in other projects. 

But first, let’s take a look a the problem a bit more. 

We want:

  1. the freedom to select any number of columns from one sheet.
  2. to select the same number of different columns from the sheet we want to paste to.
  3. to paste to the bottom (append) the receiving sheet.
  4. to take into account varying header row heights.
  5. to have a “Completed” or “Copied” column in our origin sheet so that we don’t continue to paste all the data instead of simply what has been updated.
  6. have the data with formulas that we only want to paste the values for.

The Example

Let’s say we have a sheet that is updated regularly and we want to be able to paste data from columns A,B and C of this source sheet and append it to columns C,E and A of the destination Sheet. 

  • Copy Sheet 1 Col A -> Append Sheet 2 Col C
  • Copy Sheet 1 Col B -> Append Sheet 2 Col E
  • Copy Sheet 1 Col C -> Append Sheet 2 Col A

Take a look at our two Google Sheets 

SourceSheet - Google Sheets
DestinationSheet - Google Sheets

You can see in the Source Sheet (Sheet1) that we have 3 data columns and one column market To Copy. The To Copy column will be updated with “Copied” once the code is run successfully.

In the Destination Sheet (Sheet2) you can see that we already have some data there. We don’t want to overwrite it. We simply want to append our data to the bottom of it. 

Continue reading “Google Apps Script – Copy Selected Columns in One Sheet and Add Them To The Bottom of Different Selected Columns in Another”

Google Apps Script – How to Change the File Name of Non-Native Files like MS Word Docs, PDF’s and Excel files.

Google Apps Script, DriveApp

I had an unexpected need to change some non-native file names – in my case, MS Word docs – to something else to appease my masters. Being the resident Igor, I delighted in the task. 

The Problem

I had a folder of MS Word documents on my Google Drive that needed to all be renamed. They all needed the same name with an index number to distinguish that they were different files. 

There were, however other file types in the folder. 

The problem with non-native files in Google Drive is that it’s a little tricky to find their ID. Besides I just wanted to change ALL files that were MS Word in the folder. 

What I would need to do is search for all files inside my selected folder that are of MS Word type and rename them with the same name but with a counter at the end. For example:

Old File NameNew File Name
boring.doccranberrySauce.doc
sad.doc cranberrySauce1.doc
yawn.doc cranberrySauce2.doc
Continue reading “Google Apps Script – How to Change the File Name of Non-Native Files like MS Word Docs, PDF’s and Excel files.”