We frequently need many operations in GOOGLE SHEETS when we are cleaning the data or making it more usable for us.
The applications has their limitations while performing some specific operations. That is the situation when we need some built in operations which can be taken into service whenever needed.
One such need which arises over and again is to split the cells and convert into many columns.
For Example, If we have a date, which is in text format such as
19.12.2020 which is not being recognized by Google Sheets as Date, we can use the utility TEXT TO COLUMNS directly to create three columns having Date, Month and Years separately to get our job done.
In this article, we would learn how to convert TEXT to COLUMNS in GOOGLE SHEETS.
We can use TEXT TO COLUMN in GOOGLE SHEETS under following situations.
– We have a column containing many words and we want each word to be in different columns. For Example, if we have a cell containing the Name of the employee as well as his age in the same cell and we want to separate them.
We can separate both the components into different columns.
– we want to get rid of a portion in the cell. We can use the utility to separate the data and then delete the whole column. Again concatenate the data into one.
We can find the option of TEXT TO COLUMN under DATA MENU as SPLIT TEXT TO COLUMN.
The location is shown below in the picture.
TEXT to COLUMNS:
Its a good feature in GOOGLE SHEETS which facilitates us to convert the text to columns or simply saying to segregate the text to different columns or separate the cell content into different columns on the basis of a delimiter.
Suppose we have a cell containing the text
WELCOME TO GYANKOSH (SEPARATOR IS “SPACE”)
TARGET is to segregate the above phrase into three columns which is our objective to split a cell into columns. So follow the steps to split the text into columns.
The picture above shows the result of USING SPLIT TEXT TO COLUMNS in GOOGLE SHEETS.
Although , frequently used separators are enlisted in the dropdown list, but if we need some other delimiter [ separator ] we can choose the custom option.
Let us take an example and learn to use custom separator in brief.
For Example if our cell contains the text as
These three employees’ data is to be separated into different columns.
FOLLOW THE STEPS TO SPLIT THE GIVEN TEXT INTO DIFFERENT COLUMNS:
The following screen will appear.
We can’t find the colon separator , so we need to enter it.
A small field will be opened.
In this field, enter : [ colon ].
As we put the separator, the data will be split into three columns.
The following picture shows the results.
Here are a few points to remember while using TEXT TO COLUMN option in google sheets.
OTHER WAYS TO REACH THIS ARTICLE
YOU MAY LIKE