Using the Text To Column Functionality of Excel

Do you know about the Excel Text to Column Functionality? The Text to Column functionality is one of the amazing functionality provided by Excel which allows you to move the text from one column into another and to split the text entries into two separate cells.

There are two features within the ‘Text to Column’ feature of Excel. The first one is ‘Fixed Width’ and the other one is ‘Delimiter’.

In this blog, we would learn both these functionalities of ‘Text to Column’.

How to navigate to Excel ‘Text to Column Function

To access the Text to Column, select the data set and go to the “Data” tab. Under the group, “Data Tools”, select the option “Text to Column”.

Text To Column - Navigation Path

You would see that the ‘Convert Text to Column Wizard’ would open on your screen.

This “Convert Text to Column Wizard” is a three-stepped wizard/procedure.

As you can see in the ‘Text to Column Wizard’ dialog box, there are two options available, the first one is “Delimiter” and another one “Fixed Width”.

Let us now unlock both these functionalities one by one.

How to use the “Delimited” Functionality

The delimiter functionality of this wizard splits the text in the selected cells into other cells based on the common identifier.

Below is a dataset that contains the Full Name of person. However, there is an exclamation mark (!) in the middle (separating first and the last name).

Sample Data - Full Names

To separate the first and last names from the full names, you can use the ‘Delimiter’ feature of the ‘Text to Column’ functionality of Excel.

Here are the steps.

Select the range of cells (A2:A18) and open “Convert Text to Column Wizard” by using the steps as explained in the “How to navigate to Excel ‘Text to Column Function” section of this blog.

You can see that the “Preview” section of this dialog box lists the content of the selected cells.

In the first step of this wizard, select the “Delimited” radio button as shown in the screenshot below and then click on the “Next” button.

Excel Text to Column - Delimited Option

In the second step of this wizard, there are various pre-provided delimiters such as Tab, Semi-colon, Comma, Space.

The separator in our dataset is ‘!’, therefore, select the checkbox “Other:” and in the input box, insert the exclamation symbol “!”:

Excel Text to Column - ! As Delimiter

In the “Data Preview” section of this dialog box, you would notice that between the first name and the last name, a horizontal line appears.

Now click on the “Next” button.

In the next and the last step of this dialog box, we need to select the destination cell. Destination refers to the cell where you want the split text to appear.

In the “Destination” input box, enter the destination cell as B2.

Excel Text to Column - Destination Cell

Click on the “Finish” button.

You would notice that the first and last names are now in different cells like below

Result of Text to Column Delimited
Text to Column - 'Delimited'

How to use “Fixed Width” Functionality

The fixed-width functionality of this wizard splits the text in the selected cells into other cells from the fixed width.

Below is one dataset. Here you have 10 digit numbers in column A and you want to split this number the numbers into two equal parts (i.e. 5 numbers in one cell and balance 5 numbers in other cells).

Sample Data - List of Numbers

Let us now start with using this wizard.

Select the cell range (A2:A9). And open the “Convert Text to Column Wizard” by using the steps as explained above.

Excel Text to Column dialogbox

You would notice that under the “Preview” section, the selected range of numbers is listed.

In the first step of this wizard, select the radio button “Fixed Width” and then click on the “Next” button.

Excel Text to Column - Fixed Width Option

In the second step of this wizard, you would notice a ruler above the “Data Preview” section.

Click on the ruler just after the fifth character of the numbers as shown in the screenshot below.

You would notice that a vertical line cuts the number after the fifth character.

Clicking on Fixed Width Excel

Click on the “Next” button and you would enter in the third and the last step of this wizard.

In the third step of this wizard, we need to select the destination cell. Destination refers to the cell where you want the split numbers to appear.

In the “Destination” input box, enter the destination cell as B2.

Fixed Width Destination Cell

Now click on the “Finish” button. 

You would notice that the numbers split from the fifth character and they are now in different cells as shown in the image below:

Result of Excel Text to Column Fixed Width
Text to Column - 'Fixed Width'

This brings us to the end of this blog. Share your views and comments in the ‘Comment’ section below.

Leave a Comment