How to Use the Text to Column in Excel
Learn how to separate the contents of one Excel cell into separate columns, especially when you are pulling data from other sources. Use the ‘Convert Text to Columns Wizard’. For example, when you want to separate a list of full names into last and first names. In this example also we will investigate how to take advantage of some of the most unknown features in Excel Text to Columns.
This feature is very helpful. For example, if you get data from an external program, you may see the information all cramped in one column like this example.
It would be very inefficient for you to manually separate it – would take hours!
So, let’s begin by selecting the first column by clicking on it using the left button of your mouse, in this case is column A
Now go to the Data tab, and on the upper right-hand area of the tab you will see Text to Columns, click once and the wizard will appear
The first question of the wizard asks if the data is separated by a delimiter which could be a comma, tab, or a punctuation mark. We will leave it checked and click next.
In the second step you will select what is separating the data from one another, which is the comma, check it on the list. You also want to uncheck “treat consecutive delimiters as one”, look how the names get unorganized when we do this
The third step asks you which data format you want to import your list as, for example you can say that all the information needs to be imported as General which is the default that we are using, text or date are also other options.
You also can select the header of any column individually and ask not to import it. For example, since we don’t need the Middle Name let’s click on it and check do not import. Click next when ready.
Once you click finish the data will be imported nice and neatly in Excel but let me show you another example where we have more than one delimiter.
In now the next example we have commas and semicolons right before the email address, so we will again select the first column, go to Data tab and select text to columns again.
Let’s select delimiters on your first step, since now we have a combination of commas and semicolons
The good news is that many times with just selecting the comma Excel may be able to track the emails as a separate column, but this is not the case here, the data with semicolons was treated as a single row and we need to fix that by also checking the semicolon option on the top.
Now when you click on it you want to scroll down in the preview panel and check that it all looks good. Click next for the last step.
On the third step we will skip the middle name column again as we don’t need it and click finish
Now you can organize the information better and you can do this with thousands of records simultaneously if you need to.
We hope you found this tutorial helpful and don’t forget to like and subscribe to support our YouTube channel and keep making more free content for you.
how to convert column to text with delimiter in excel
how to separate words in excel using formula
excel text to columns formula automatically
advanced text to columns
how to separate text in excel
excel text to columns shortcut
excel formula split text by delimiter
text to columns in excel not working