PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
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.
When
– 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.
Andrew 45
Ravi 24
Daniel 34
Cameron 44
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.
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.
EXAMPLE
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.
STEPS:
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
ANDREW:23:NEWYORK
DANIEL:27:CHICAGO
JOHN:38:CHICAGO
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.
Click CUSTOM.
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