Table of Contents
- INTRODUCTION
- WHERE IS TEXT TO COLUMN IN EXCEL ?
- EXAMPLE : DIVIDE THE GIVEN TEXT AND PUT IN DIFFERENT COLUMNS [ CONVERT TEXT TO COLUMNS]
- FAQs
- HOW TO REMOVE DUPLICATE ROWS IN EXCEL USING THE REMOVE DUPLICATES OPTION?
- WHICH COPY OF THE DATA IS KEPT AND WHICH COPY IS DELETED BY REMOVING DUPLICATES IN EXCEL?
- HOW TO REMOVE DUPLICATE CELLS IN EXCEL?
- CAN WE REMOVE DUPLICATES BY CHECKING THE DUPLICITY IN THE ROWS?
INTRODUCTION
Excel is full of READY-TO-USE functionalities which are needed frequently.
One such functionality is TEXT TO COLUMN.
TEXT TO COLUMN is a good feature in Excel that 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.
In this article, we’ll learn the way to make use of this readymade functionality.
WHERE IS TEXT TO COLUMN IN EXCEL ?
We can find the functionality to convert TEXT TO COLUMN under the DATA TAB> TEXT TO COLUMN under DATA TOOLS.
The following picture shows the button location.
The following picture shows the TEXT TO COLUMN option in EXCEL 2007.
EXAMPLE : DIVIDE THE GIVEN TEXT AND PUT IN DIFFERENT COLUMNS [ CONVERT TEXT TO COLUMNS]
Let us take an example to understand the usage of this function.
We’ll demonstrate it with two different examples.
- WHEN THE SEPARATOR IS A SPACE
- WHEN THE SEPARATOR IS A COMMA
CONVERT TEXT TO COLUMN WHEN SEPARATOR IS A SPACE
EXAMPLE
Suppose we have a cell containing the text
WELCOME TO GYANKOSH (SEPARATOR IS “SPACE”)
We intend to segregate the above phrase into three columns. [ i.e. WELCOME, TO and GYANKOSH ]
:
- Select the cell containing the text. (A full column can be there but for our example, we are taking a cell only).
- Click TEXT TO COLUMN button. A dialog box will open.
- After clicking NEXT, the next screen will appear, which is self-explanatory.
- We are going to split the text into three columns. Click NEXT.
- After clicking next, the output appears.
- CLICK FINISH.
The output is shown below.
CONVERT TEXT TO COLUMN WHEN SEPARATOR IS ” , . OR , OR ANYTHING ELSE:
Let us take the example of the text string ” WELCOME.TO.GYANKOSH” in which the separator is not a space but a dot [ . ].
STEPS TO CONVERT TEXT ON THE BASIS OF. SEPARATOR AND PUT INTO DIFFERENT COLUMNS:
- Select the cell containing the text WELCOME.TO.GYANKOSH.
- Click DATA TAB and click TEXT TO COLUMN BUTTON.
- CHOOSE DELIMITED OPTION as already described and click NEXT.
- The NEXT dialog box is shown below.
- Choose OTHER and type a dot [.].
- Click NEXT.
- Enter the destination cell and click FINISH.
The output is shown below.
FAQs
HOW TO REMOVE DUPLICATE ROWS IN EXCEL USING THE REMOVE DUPLICATES OPTION?
Follow the standard steps as discussed.
Choose all the columns for the DUPLICATE CHECKING CRITERIA when EXCEL asks the columns which will be checked for the repeated values. If we check all the columns, all the parameters will be checked and only unique rows will be left out.
It’ll remove all the rows in Excel.
WHICH COPY OF THE DATA IS KEPT AND WHICH COPY IS DELETED BY REMOVING DUPLICATES IN EXCEL?
The first copy of the data will be preserved while the repeated value will be deleted or removed.
HOW TO REMOVE DUPLICATE CELLS IN EXCEL?
We can make use of the standard procedure to remove all the duplicate cells in Excel.
Simply select all the cells, go to DATA TAB and choose REMOVE DUPLICATES.
Select the column criteria to choose the columns which need to be checked for duplicates.
CAN WE REMOVE DUPLICATES BY CHECKING THE DUPLICITY IN THE ROWS?
No, the default REMOVE DUPLICATES option doesn’t allow us to check the values in the rows.
