Text to columns is a quick and easy way to split out one set of data into multiple columns to work with the data more effectively. Let’s walk through what it is, when to use it, and an example to put it into action.
What Is It and Where To Find It?
Text to Columns is a feature in Excel that splits the text in one column into multiple columns. The split takes place using “delimiters.” A delimiter is a fancy way of saying a symbol, space, or value that is repeated across your data. Excel uses the delimiters to parse out, or split, the data.
Some examples of a delimiter are a space between a first and last name, an @ in an email address, or a comma in a CSV file.
You can find this feature towards the middle of the Data tab on the ribbon. You can also access it using the shortcut ctrl + alt + a + e.
When To Use It?
There are many ways to put this feature to work. Some common ways include:
- When you need to work with the data in a CSV or TXT file
- To split names into first names and last names
- Convert dates to correct format
- Convert text to numbers or numbers to text
Really, anytime you have data grouped together and want to split it out, you can likely put Excel to work.
Let’s say you are given this list of 100 names, but you need the first and last names separated to work with the data. No reason to do this by hand, we can put Excel to work.
First, select the data field that you want to adjust. You can either select the data set, or an entire column if needed. Then, hit the Text to Columns button on the data tab of the ribbon. Step 1 will pop up and you will almost always want to select “delimited.”
Then, you will move on to step 2. Here you can choose the delimiter. Excel has preset options including tab, semicolon, comma (very useful to parse CSV files), and space. You can also create your own such as a /, an @, or any other value or symbol. We will select space since each name has a space in the middle.
After that, move on to step 3 where you can select the format. In this case, general works great. If you want to adjust date formats or convert between text and numbers, select that option here. Under “preview of selected data” you can adjust each column individually by clicking on the column. You can also select the destination cell for each column, where you want Excel to move the data once it is split.e
Finally, hit finish and review the data for accuracy.
Bonus Tip: You can use remove duplicates on the data tab to make sure there are no repeats in your data set.
Have any questions on text to columns in Excel? Are there other topics you would like us to cover? Leave a comment below and let us know! Make sure to subscribe to our Newsletter to receive exclusive financial news right to your inbox.