How to Convert Text to Columns in Excel (3 Cases)

Excel’s Text to Columns Wizard is a very important feature with many uses. In this article, I will teach you how to convert text to columns in Excel. Like you can split the columns of your database in your own way, you can extract important information from your email addresses. Even I have also shown how you can use the advanced option of Text to Columns Wizard to convert trailing minus signs to negative.

Download Practice Workbook

You can download the practice book from the link below.

3 Practical Cases to Convert Text to Column in Excel

In this section, you will find 3 practical cases to convert text to columns in Excel. I will discuss them with proper illustrations here one by one. Let’s check them now.

1. Split Columns Using Text to Column Feature

When you design your database in Excel to store information in it, you have to remember one thing very carefully. Your every column should have a minimum amount of data in it.

Databases are powerful only if you make them powerful. How can you do that? How can you avoid the problems mentioned upstairs?

Let’s elaborate on an example and work around diagnosing the problems in the first place.

This is a sample dataset overview. In this dataset, we have some information about some person (i.e. their Contact Name, Address, Date of Birth, etc.)

So our really important database is ready. Now I will be able to manipulate it to make it even more helpful. I want to arrange the names alphabetically.

Problem here? The first name and last name are together in the same columns.

I want to know how many people from Illinois are there on my list. Problem here? The whole address has been fed in one column.

I want to know how many 20-30 years olds I have.  You got it right. Cannot be done. Same column.

So you got the pattern, right? There is too much information in each column.

Had they been in different columns, it would have been much easier.

To solve these types of problems: you can use three tools:

1. You can use the Text to Columns feature in Excel
2. You can use the Flash Fill feature. Flash Fill feature is a new tool for Excel 2013
3. And you can also use the Excel function to divide column data into ultimate small pieces.

To know how the Flash Fill feature works in Excel, read this article: How to use Flash Fill in Excel

Similarly, the 3^rd option is also an entire topic covered here.

We will be focusing on the Text to Columns feature.

1.1. Splitting the Names

Let’s first work with the Contact column. Follow the steps below.

Steps:

• First of all,  select the whole column. You can select the whole column by using the mouse or another way is to use the keyboard. Just press CTRL + SHIFT + Down Arrow for this and the whole column gets selected pretty easily.
• Now open the Data ribbon and in the Data Tools group of commands, click on the Text to Columns feature.

• Here, Convert Text to Columns Wizard box will appear. This is showing Step 1 of 3. By default, the Delimited radio button is selected. Click Next.

• After that, we enter Step 2 of 3. Here we see some Delimiters are available. We know that Space is the Delimiter in our case. You will also be able to see a data preview in this window. Scroll down to see where the separated data is okay. Click Next.

• You can choose Column data format to General, by default it is selected.
• If you select a date, you can also choose your format for the date, by default DMY is selected in my case ( DMY means Day, Month, and Year), but there are other options in the dropdown.
• Select the Destination field where your data will be set (i.e. G4 cell).
• Now click Finish.

• And here is the final outcome.

You see the Contact column is separated into two columns. I name the first column as First Name and the second column as Last Name. So, separating data from a single column containing a Name using the Text to Columns feature is not that tough.

1.2. Splitting Addresses

Let’s say you try to separate the City State and ZIP column into its smaller Components: City, State, and Zip. See this column has two types of delimiters: Comma and Space. In this case, you have to separate it into two steps.

To do so, follow the steps below:

• At first, I separate this column using the Comma delimiter. (Open the Wizard following Method 1.1)

• Here, your column is divided into two parts. We name the first column as City and the second as State Zip.

• Now we shall divide the column called State Zip using the Space Delimiter.

So easy! Isn’t it?

1.3. Convert Text to Date

Let’s try to work on the Date of Birth column.

The first part of this column is Month> then day and then> year. Here we are going to use the Date format.

• Firstly, select the Date of Birth column and open the wizard like Method 1.1.

• Here, from this column data format, I select the Date button and then select the MDY option from the drop-down> change the Destination cell (i.e. L4). And finally> click on the Finish button.

• Hence, you will see how nicely the column has been converted to a date column.

There you go!! You see now how easy it is to use the Text to Columns feature to redesign an eye-distracting database.

2. Extract Domain and Username from Email IDs Using Text to Column in Excel

The Text to Column feature provides an easy option for splitting Email Addresses into Username and Domain.

To perform this, proceed as below.

Steps:

•  Firstly,  select the email ids that you want to be split and open the Convert Text to Columns Wizard like Method 1.1.

In Step 1 of Converting Text to Column Wizard select Delimited and click Next.

• Now, in the 2nd step of the Converting Text to Column Wizard, put a tick mark on the Other option and write @ just beside it. Press on to the Next option.

• After that, in Step 3, set the Destination (i.e. C2:D8) and click Finish.

• You will see the Usernames and Domains are divided into two columns.

3. Advanced Text to Columns for Negative Numbers

Suppose you have numbers in a column with a trailing minus sign.

To convert text to columns in Excel for this negative numbers, select the numbers and begin to perform the Text to Column feature.

Steps:

• Firstly, open the wizard like Method 1.1 and in step 1 of Converting Text to Column Wizard select Delimited and click Next.

• In Step 2 in the Delimiters option, select nothing and press Next.

• In Step 3, press on the Advanced option. Now, in the Advanced Text Import Settings, put a tick on the Trailing minus for the negative numbers and click OK.

• After that, set the Destination and click Finish.

• Finally, you will see the below result after this.

Conclusion

In this article, I have tried to show you some methods to convert text to columns in Excel. Hope you like the article. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box. For more queries, kindly visit our website. Keep in touch!

Happy Excel! 🙂