Learn how Excel's Text-to-Columns feature efficiently separates combined data such as cities and states or first and last names into distinct columns. This article demonstrates the practical steps and considerations for optimizing data sorting and organization, saving valuable time.
Key Insights
- Use Excel's Text-to-Columns tool to split combined data into separate columns, enabling effective sorting; for example, separating cities and states by choosing the comma delimiter within the "Delimited" split option.
- Implement the "Proper" function in Excel to capitalize text uniformly before splitting columns, enhancing data consistency and readability; type "=PROP", press tab, select the desired cell, and copy the function results as values.
- The article illustrates additional flexibility with Text-to-Columns, including splitting first and last names separated by spaces, further demonstrating the tool's adaptability to various data formatting scenarios.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
Text-to-columns. In this section, we'll cover text-to-columns. What does text-to-columns do? Well, it sounds like what it's called.
You want to take text and you want to move it to other columns. I have a situation here where I have several cities and states. I may be requested to sort this list by state.
But looking at the way the information is set up in the spreadsheet, it's going to be impossible for me to do that because the cities and the states are all in one cell. In order for me to be able to sort by state, I would need to write the states in a separate column and sort them there. But I don't want to retype all the states and then delete them from the original list.
So I'll use text-to-columns. Text-to-columns will allow me to move the states over to a separate column. The first step is to select the information that you would like to split.
Then you'll head over to the data tab and you'll choose in the data tools group, you'll choose text-to-columns. When you click text-to-columns, you'll see a three-step wizard. The first part of the wizard is going to ask you to choose how you would like to split your information.
You can choose to split the information based on a delimited split or a fixed width split. If we choose fixed width, just to show you what it would look like, you'll be taken to this screen where you'll be able to move this line to exactly the point where you want the information to be split. You can also add several lines if you want.
This would not be ideal for this type of data because the cities and states have variable character lengths. They're not all the same size. So I'm going to go back and choose delimited.
When you choose delimited, characters such as commas or tabs separate each field. I'll click next. Here I get to choose the character that is going to separate cities and states.
Now that's not the tab, obviously. So it's going to be the comma. As soon as I choose the comma, I get a preview of the information and how it's going to look in my spreadsheet.
And I can see that the cities and the states have been separated. I'll click next. This particular section is not necessary for the work we're doing here.
But if you're importing a CSV file, you can choose to change the file type as the information comes in. And you can also choose not to import certain columns. I'm going to click finish and look at my result.
And here I have the result of the cities and the states broken up into two different columns. Now, if I wanted to sort by state, I could. In the class, we give students the opportunity to do this with first names and last names.
So I start out by showing them how they can properly capitalize the names before they do this. So I'll briefly do this. This is going to utilize a function called proper.
I'll type in equal, P-R-O-P, then I'll press TAB. If you press TAB, you don't have to enter the entire function. And the bonus is Excel adds in the open parentheses.
I'll use the right arrow key to select the cell over to the right and press ENTER. Then I'll head over to the cell and I'll simply double click on the black plus sign. This copies that formula all the way down.
And then I'll press control C. Head over to the column where I need to have the names properly capitalized. Bring up a special. And choose V for values.
As soon as I press ENTER, I paste the result of the proper function into the first name column. Now that the function has served its purpose, I can press escape. Select the entire column.
I'll use control shift down and then simply press delete. Now I'm ready to split the information. So I'll start with Jesse Bennett.
Control shift down. Head over to text to columns. Delimited is the option we're going to choose here.
I'll click next. I get to choose the character that's going to split the first name and last name. I'll choose space.
Then I'll take away the comma. Because we don't have any commas here. And I could go to the third dialog box, but I. Third part of the dialog box, but I don't need to in this case.
I'll simply click finish. And now I've successfully split the first names and last names in this exercise. So that is an example of using text to columns to break up information in your spreadsheet.