Excel Text to Columns

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. 

Cities Example

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 gonna 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 the 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 the columns when you click text to columns. 

3 Steps

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 limited split or a fixed with split. 

If we choose fixed with 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. 

Now 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 to 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. 

Proper Function

This is going to utilize a function called proper. I'll type an 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. 

He's 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 pay 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. 

The columns delimited is the option we're gonna choose here. I'll click next. I get to choose the character that's gonna 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.