HOW TO CONVERT TEXT TO COLUMN IN EXCEL?

Table of Contents

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.

TEXT TO COLUMN OPTION IN EXCEL 2010,2013,2016,2019

The following picture shows the TEXT TO COLUMN option in EXCEL 2007.

TEXT TO COLUMN BUTTON LOCATION

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 ]

:

  1. Select the cell containing the text. (A full column can be there but for our example, we are taking a cell only).
  2. Click TEXT TO COLUMN button. A dialog box will open.
CHOOSE FIXED WIDTH AND CLICK NEXT
  1. After clicking NEXT, the next screen will appear, which is self-explanatory.
CLICK NEXT
  1. ​We are going to split the text into three columns. Click NEXT.
  2. After clicking next, the output appears.
CHECK THE OUTPUT FORMAT AT THE BOTTOM AND CLICK FINISH
  1. CLICK FINISH.

The output is shown below.

OUTPUT [ THE TEXT IS SPLIT INTO COLUMNS ]

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.
CHOOSE THE APPROPRIATE SEPARATOR [ DOT IN OUR CASE ] AND CLICK NEXT
  • Enter the destination cell and click FINISH.
CHECK THE FINAL OUTPUT AND CLICK FINISH

The output is shown below.

FINAL OUTPUT



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.