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.

Convert Text to Column in Excel: 3 Practical Cases

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. 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.)

Dataset to Convert Text to Columns in Excel

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-year-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. You can also use the Excel function to divide column data into ultimate small pieces.

Similarly, the 3rd 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.


  • 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.

Text to Columns Feature in Excel

  • 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.

Convert Text to Columns Wizard Box in Excel

♦♦Note: For our case, the Fixed Width option is not applicable. You can select that when you are sure of the width of the new columns and know the fact that the data you want will be truncated properly just by going by dimensions. So Delimited is okay with our problem.
  • 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.

Close Convert Text to Columns Wizard box

  • 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.

Read More: How to Split Text to Columns Automatically with Formula in Excel

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 separated this column using the Comma delimiter. (Open the Wizard following Method 1.1)

Set Comma as Delimiter in Convert text to Columns Wizard Box

  • 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.

Space Delimiter in Convert Text to Columns in Excel

So easy! Isn’t it?

Read More: How to Use Text to Columns Feature with Carriage Return in Excel

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 following 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.

Read More:  How to Use Text to Columns in Excel for Date

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.

Convert to Column for Email Address

To perform this, proceed as below.


  •  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.

Read More: How to Convert Text to Columns in Excel with Multiple Spaces

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 these negative numbers, select the numbers and begin to perform the Text to Column feature.


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

Step 1 of Convert text to Column Wizard in Excel

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

Step 2 of Convert text to Column Wizard in Excel

  • 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.

Advanced option in Excel Convert text to Column Wizard

  • After that, set the Destination and click Finish.

Set Destination in Convert Text to Column Wizard in Excel

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

Convert Negative Numbers

Read More: How to Convert Text to Columns with Multiple Delimiters in Excel

Download Practice Workbook

You can download the practice book from the link below.


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. Keep in touch!

Happy Excel! 🙂

Related Articles

<< Go Back to Excel Text to ColumnsSplitting TextSplit in ExcelLearn Excel

Get FREE Advanced Excel Exercises with Solutions!
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF