HOW TO SEPARATE LAST NAME AND FIRST NAME IN GOOGLE SHEETS?

Table of Contents

INTRODUCTION

GOOGLE SHEETS is a spreadsheet application offered by Google.

A spreadsheet application is very helpful in manipulating data and text with the help of given functionalities and functions by which we can create useful reports and analyze the data.

These application help us analyze of manipulate thousands of rows with few steps. But even after the abundant options available within the application, we’ll always find new situations which doesn’t have any direct solution.

In such cases we need to apply some tactics and get our things done.

One such situation is when we are given the complete names within the same cell and we need to separate them.

We can separate the last name and first name using a few ways which we’ll discuss in this article.

PURPOSE OF SEGREGATING LAST NAME AND FIRST NAME IN GOOGLE SHEETS

Many times , while copying the data from any source and putting it in the sheet for further analysis, we get stuck in this situation.

If we have the last name and first name separated, we can make many adjustments which are more flexible than the previous situation where we have the complete names within single cells.

For example, if we have the last name and the first name separated, we can sort the data on the basis of last name as well as first name whereas if the name is combined, we won’t be able to sort the data as per our requirement.

Similarly, we can customize the format as ” FIRST NAME LAST NAME” or ” LAST NAME FIRST NAME” very easily.

In addition to this, there are many other benefits of having the separate names.

HOW TEXT IS HANDLED IN EXCEL?

TEXT is simply the group of characters and strings of characters which convey the information about the different data and numbers in Excel. Every character is connected with a code [ANSI].

Text comprises of the individual entity character which is the smallest bit which would be found in Excel.

We can perform the operations on the strings[Text] or the characters.

Characters are not limited to A to Z or a to z but many symbols are also included in this which we would see in the later part of the article. 

TEXT IS AN INACTIVE NUMBER TYPE[FORMAT] IN EXCEL. ANYTHING STORED AS TEXT [NUMBER OR DATE] WON’T RESPOND TO ANY STANDARD FORMULAS OR FUNCTIONS BUT SPECIALLY DESIGNED TEXT FUNCTIONS. [EXCEPTIONS DO OCCUR IN CASE OF NUMBERS]

If we need to make anything inactive, such as Date to be non responding to the calculation, we put it as a text. Similarly if we want to avoid any calculations for a number it needs to be put as a text.  

WAYS TO SEPARATE LAST NAME AND FIRST NAME IN GOOGLE SHEETS

We can use many available ways to separate last name and first name in excel.

Few of the ways are as follows.

  • TEXT TO COLUMN– We can split the text in a cell into the different columns. The basis of the separation can be any delimiter like a comma, colon etc.
  • USING FUNCTIONS

TEXT TO COLUMN:

We’ll discuss it here briefly as a complete detailed topic is already present.

HOW TO CONVERT TEXT TO COLUMN IN GOOGLE SHEETS ?

Text to column is used in a situation when we need to split any string [sentence of few words] separated by any delimiter [ comma, colon , full stop, semicolon, space etc. The process is backed up by a wizard mechanism which makes it very simple. The only requirement is that it needs a delimiter, some character on the basis of which it’ll separate the string into words.  

USING FLASHFILL:

FLASH FILL is a new tool for many kind of task which would otherwise take much time. For such tricky tasks such as separating the words or auto filling or many such kind of tasks which has some pattern are well suited for the FLASH FILL. It is a complete topic discussed in detail.

CLICK HERE TO CHECK OUT THE COMPLETE ARTICLE ON FLASH FILL IN EXCEL.

USING FUNCTIONS:

There are a few very important functions which help us to separate the last name and first name simply. [Example discussed below ].

SEPARATING LAST NAME AND FIRST NAME IN EXCEL

Let us now take an example and learn the step by step procedure of using the different ways learnt above.

EXAMPLE DATA:

Suppose we have a column with the first and last name put in the same cell.

If we want to apply any kind of formula on this, we need to separate or split the first and last name.

Suppose the data given is

Kim Rogers

Daniel Bros

Allison Berge

Joe Wilson

Ravi Kumar

Ashfaq Akhtar

EXAMPLE DATA

We can do that using the following methods.

1. USING TEXT TO COLUMN TO SPLIT NAMES IN GOOGLE SHEETS–

We can use this trick confidently and easily to separate the first and last name within seconds. [CLICK HERE TO LEARN TEXT TO COLUMN WITH PICTURES AND EXAMPLES].

FOLLOW THE STEPS TO SPLIT THE NAME INTO LAST NAME AND FIRST NAME USING TEXT TO COLUMN OPTION

  • Copy the names from column D to column E which will be the column containing the first name as the result.
  • Go to DATA and choose SPLIT TEXT TO COLUMNS as shown in the picture below.

CHOOSE SPLIT TEXT TO COLUMNS
  • As we choose the option, the column will be selected and a popup will open to choose the separator. [ Use link for the detailed explanation.]
  • Choose SPACE from the drop down menu.
  • The name will be separated into first name and last name as shown in the picture below.
  • After applying the text to column, we will be having the following data.
SPLITTING NAME INTO LAST NAME AND FIRST NAME

In this way, we can split the names into last name and first name using TEXT TO COLUMN option.

2. USING FUNCTIONS TO SPLIT NAMES INTO LAST NAME AND FIRST NAME

After the automated options let us try now the manual one, of course using the functions.

Here are our requirements.

A function which can

  • Search any character [We can use SEARCH function or FIND for the same.
  • Functions which can extract characters from the TEXT. [LEFT, MID AND RIGHT FUNCTION are the best contenders for this task].
  • Function which can return the length of the text. [LEN FUNCTION is suitable for this task].

With the use of these function , let us try to separate the text in Excel.

EXAMPLE 1: SPLITTING THE NAMES USING FUNCTIONS IN EXCEL:

Let us try our same example first.

STEPS TO SPLIT THE NAMES WHICH CONTAIN SPACE AS THE SEPARATOR
  • Select the cell where we want the first name.
  • Enter the formula as =LEFT(D17,SEARCH(” “,D17,1)-1) where D17 is the cell containing the NAME. [In this step we use the LEFT function to extract the characters from the left. We take the first input as the cell containing the complete name,. The second input is taken with the help of Search function which will compare space ” ” in the complete name from the character 1 and return the position of the space, which will become the length of the characters from the left.
  • Enter the formula =RIGHT(D17,LEN(D17)-SEARCH(” “,D17,1)) [This function works on the same principle as the previous step. The function is RIGHT and takes the first argument as the complete name which is in D17 for our example. The LEN(D17) returns the complete length of the string. We subtract the location of the SPACE ” ” using the same method which is using the SEARCH FUNCTION. This combination gives us the location of the starting of the second word.
  • Drag both the formulas down the column.
  • The result is shown in the pic.
SEPARATING FIRST AND LAST NAME IN EXCEL USING THE FUNCTIONS

EXAMPLE 2: SPLITTING THE GIVEN NAME INTO FIRST NAME, MIDDLE NAME AND LAST NAME

We just learnt how to separate the Names in two different cells. Now let us learn to separate the names which contain a Middle name into Three different portions.

STEPS TO SPLIT THE NAMES WHICH CONTAIN MIDDLE NAME TOO.

  • Select the cell where we want the first name.
  • Enter the formula as =LEFT(H4,SEARCH(” “,H4,1)-1) where H4 is the cell containing the NAME. [In this step we use the LEFT function to extract the characters from the left. We take the first input as the cell containing the complete name,. The second input is taken with the help of Search function which will compare space ” ” in the complete name from the character 1 and return the position of the space, which will become the length of the characters from the left.
  • For the middle name we need to use the MID FUNCTION. Select the cell for the MIDDLE NAME and put the following formula =MID(H4,SEARCH(” “,H4,1)+1,SEARCH(” “,H4,SEARCH(” “,H4,1)+1)-SEARCH(” “,H4,1)) . [The function starts with MID as the top function. The first argument is the H4 which is the cell containing the complete name. The second argument is the starting character sequence number. We used SEARCH FUNCTION and try to search ” ” in H4. We add one to this as we need the first letter of the middle name. The third argument is the number of letters to be taken for the middle name. We found it out using the SEARCH FUNCTION again but started the SEARCH FROM THE NEXT SPACE” ” which would return the space after the middle name or second word. Subtract this position from the position of the first space. Now we have the number of letter we want.
  • The LAST NAME or the third word can be found by using the following formula. Enter the formula in the LAST NAME COLUMN AS =RIGHT(H4,LEN(H4)-(SEARCH(” “,H4,SEARCH(” “,H4,1)+1))) [This function works on the same principle as the previous step. The function is RIGHT and takes the first argument as the complete name which is in D17 for our example. The LEN(D17) returns the complete length of the string. We subtract the location of the SPACE ” ” using the same method which is using the SEARCH FUNCTION. This combination gives us the location of the starting of the second word.
  • Drag both the formulas down the column.
  • The result is shown in the pic.
SEPARATING FIRST AND LAST NAME IN EXCEL USING THE FUNCTIONS