PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

HOW TO USE SPLIT FUNCTION IN GOOGLE SHEETS

INTRODUCTION

SPLITTING is required frequently in any spreadsheet while manipulating the text.

Splitting means to break any text into two or more parts on the position of any delimiter or any character.

So, we have this handy function named SPLIT which helps us to dynamically apply the automation in our reports.

In this article, we would learn about the SPLIT FUNCTION in GOOGLE SHEETS , its syntax formula, purpose and usage with the help of practical examples.

PURPOSE OF SPLIT FUNCTION IN GOOGLE SHEETS

SPLIT FUNCTION helps to divide the text into many fragments around a character or delimiter and put them into different columns in the same row.

For Example, If we want to split a text ” How are you ” into three different columns containing HOW , ARE AND YOU separately, we can make use of this function.

We’ll see how to use it in the examples below.

PREREQUISITES TO LEARN SPLIT

THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.

Basic understanding of how to use a formula or function.
Basic understanding of rows and columns in GOOGLE SHEETS.
Of course, access to GOOGLE SHEETS.

SYNTAX: SPLIT FUNCTION IN GOOGLE SHEETS

The syntax ( the way how formula is phrased for google sheets) of SPLIT is

= SPLIT ( TEXT , DELIMITER , IF EACH CHARACTER IS TO BE CONSIDERED FOR SPLITTING , IGNORE EMPTY CELL )

TEXT The text to be divided. It can be a word or a group of words or Address of cell containing text.

DELIMITER is the character or group of characters [ pattern ] around which we need to divide the text.

IF EACH CHARACTER IS TO BE CONSIDERED FOR SPLITTING If we have a pattern delimiter e.g. ” the ” whether splitting will take place on every t h and e or “the” only. If TRUE, division shall take place around every character of the delimiter.

IGNORE EMPTY CELLS [OPTIONAL ] If TRUE, it’ll remove the empty entries of the text due to delimiters and keep only the columns with text.

EXAMPLE 1: SPLIT THE NAMES AT THE SPACES INTO FIRST NAME AND LAST NAME

DATA SAMPLE

Let us take an example and split the names into first name and last name using the SPLIT FUNCTION.

The example data is following.

OBJECTIVE

TO SPLIT THE NAMES INTO FIRST NAME AND LAST NAME USING THE SPLIT FUNCTION.

STEPS TO APPLY SPLIT FUNCTION

Have a look at the syntax formula now.

The formula will go like =SPLIT ( TEXT , delimiter [ space in this case ] , true [ no need of character level breaking as space is single character , TRUE [ no need of empty columns ])

FOLLOW THE STEPS

Select the cell E10.
Put the formula as =SPLIT(C10,” “,TRUE,TRUE).
Click ENTER.
The First name and Last name will be separated and placed in the adjacent cells as shown in the picture below.

Copy the formula from the cell E10 by selecting the cell and pressing CTRL+C.
Paste the formula by selecting the cell and pressing CTRL+V in the cells E12, E14 and E16.
The result will appear as shown in the picture below.

*We could have dragged down the function but we have a line gap in our names which is not recommended but to make the example readable, we have done this intentionally.

The above picture shows the final result.

All names are split as we expected at the delimiter which was a SPACE in this example.

EXPLANATION

The formula used is =SPLIT(C10,” “,TRUE,TRUE).
The first argument is C10 which is the address of the cell containing the text.
The second argument is a SPACE PUT INSIDE THE INVERTED COMMAS which is our delimiter or separator for our example, where we want to split our text.
The third argument is TRUE , which will separate the text at each SPACE.
The fourth argument is TRUE which will ignore the cells having no text.

EXAMPLE 2: SPLIT THE sentence AT THE given pattern as delimiter

DATA SAMPLE

We already solved an example which was straightforward and easy to do.

Let us now take an example , a bit difficult, in the form of a sentence and split it at a pattern

with splitting at the pattern.
with splitting at each character of the pattern.

The example data is following.

The text is in the form of a sentence

IT IS THE MOST BEAUTIFUL FLOWER I HAVE EVER SEEN

We’ll split this text with

Characters T and H.1.
Pattern TH

FOLLOW THE STEPS TO SPLIT THE TEXT ON THE BASIS OF OCCURRENCE OF CHARACTERS T AND H

Select the cell where we want the result. For our example, select the cell G28.
Put the formula as =SPLIT(C28 ” TH “, TRUE , TRUE)
Press Enter.

FOLLOW THE STEPS TO SPLIT THE TEXT ON THE BASIS OF OCCURRENCE OF PATTERN TH

Now, for Pattern TH , select the cell G33 and put the formula as=SPLIT(C28, “TH” , FALSE ,TRUE)
Press Enter.
Both the results are visible in the picture below.

EXPLANATION

Let us understand the formula used:

SPLIT THE TEXT ON THE BASIS OF OCCURRENCE OF CHARACTERS T AND H

We used the formula as =SPLIT(C28 ” TH “, TRUE , TRUE)
The first argument is the cell containing the Text i.e. C28.
The second argument is the pattern which will the basis of the splitting.
The third option is TRUE which will split the text on every occurrence of T or H.
The fourth argument is TRUE while will ignore the empty cells.

SPLIT THE TEXT ON THE BASIS OF OCCURRENCE OF PATTERN TH

We used the formula as=SPLIT(C28, “TH” , FALSE ,TRUE)
The first argument is the cell containing the Text i.e. C28.
The second argument is the pattern which will the basis of the splitting.
The third option is FALSE which will split the text on every occurrence of the PATTERN TH.
The fourth argument is TRUE while will ignore the empty cells.

Contents

HOW TO USE SPLIT FUNCTION IN GOOGLE SHEETS

INTRODUCTION

PURPOSE OF SPLIT FUNCTION IN GOOGLE SHEETS

PREREQUISITES TO LEARN SPLIT

SYNTAX: SPLIT FUNCTION IN GOOGLE SHEETS

EXAMPLE 1: SPLIT THE NAMES AT THE SPACES INTO FIRST NAME AND LAST NAME

DATA SAMPLE

OBJECTIVE

STEPS TO APPLY SPLIT FUNCTION

EXPLANATION

EXAMPLE 2: SPLIT THE sentence AT THE given pattern as delimiter

DATA SAMPLE

EXPLANATION

WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL