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.
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.
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
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.
Let us take an example and split the names into first name and last name using the SPLIT FUNCTION.
The example data is following.
TO SPLIT THE NAMES INTO FIRST NAME AND LAST NAME USING THE 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
*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.
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
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
FOLLOW THE STEPS TO SPLIT THE TEXT ON THE BASIS OF OCCURRENCE OF CHARACTERS T AND H
FOLLOW THE STEPS TO SPLIT THE TEXT ON THE BASIS OF OCCURRENCE OF PATTERN TH
Let us understand the formula used:
SPLIT THE TEXT ON THE BASIS OF OCCURRENCE OF CHARACTERS T AND H
SPLIT THE TEXT ON THE BASIS OF OCCURRENCE OF PATTERN TH
OTHER WAYS TO REACH THIS ARTICLE
YOU MAY LIKE