Excel Text To Columns Feature

This Tech Tip was submitted by Mary Jo Geise, Chair and Professor of Computer Science, University of Findlay 

There are times when using Excel that you may need to split a column of information into finer pieces of data. Examples of this include splitting full names into first, middle, and last names or splitting city, state, and Zip Code into their individual pieces. While you could do this with string functions, Excel has a Text to Columns option under the Data Tab that greatly simplifies this task. 

Consider the data example shown in Figure 1: 

TtC1a.JPG

Figure 1 

The first task is to separate the FullName column into first, last, and middle initial. Because the last name Van Dyke contains a space and some of the last names have suffixes such as III and Jr., it is necessary to separate the last name from the first name at the comma. To split the data, follow the steps below.  

Creating the Last Name Field 

  • Add one blank column (B) between the current FullName and Address columns to accommodate the split of first and last names. Do this by highlighting the current B column, right-clicking and choosing Insert from the shortcut menu. If you have commas before the suffix (i.e., Van Dyke, Jr., Wayne) then you would want to add two blank columns.
  • Select the data in the FullName column and click the Text to Columns option in the Data Tools group under the Data Tab as shown in Figure 2. This will open the Convert Text to Columns Wizard. 

Figure 2
  • On step 1 of the Text to Columns Wizard, make sure that you have Delimited selected. Click Next.
  • On step 2 of the Wizard, select Comma as the delimiter. Click Next.
    On step 3 of the Wizard, you can pick Text for the data format or allow it to default to text as indicated in the definition of General. Click the Finish button to complete this conversion. 

The resulting data has the last name and suffixes in Column A and the first name and middle initials in Column B. 

Creating the First Name and Middle Initial Fields 

The next step is to separate the middle initial and remove the period after the middle initial (if it exists) by following the steps below.  

  • Add a new column after column B by highlighting the current C column, right-clicking, and choosing Insert from the shortcut menu. Select the data in Column B and select Text to Columns in the Data Tools group under the Data Tab.
  • In step 1 of the Wizard, select Delimited and click Next.
  • In step 2 of the Wizard, select Space and Other in the Delimiters group, typing a “.” in the other box as shown in Figure 6 below. Make sure that the Treat consecutive delimiters as one is checked and then click Next.   
  • In step 3 of the Wizard, click Do not import the column as shown in Figure 7 for the first column as it is a blank column due to the blank character left before the first name. For the second and third columns, you may change the data format to Text or leave it at General which will automatically convert the data to text. Click Finish.
  • Resize and give meaningful column headings to the newly created columns. Your data should now look like Figure 3: 
 

Figure 3 

Creating the City Field 

  • To convert the City, State, and Zip Code column into three distinct columns, select the data in column E as shown in Figure 4 and choose the Text to Columns option under the Data Tab. 
 

Figure 4

  • Since some of the city names have a space in them, we will need to split the city, state, and Zip Code in two steps rather than in one. Split the city from the state and Zip Code first.
  • In step 1 of the Wizard, make sure Delimited is selected and click Next.
  • In step 2 of the Wizard choose the Comma delimiter as you did in creating the Last Name field and click Next.
  • In step 3 of the Wizard, leave the data format at General or Text and click Finish. The city is now in column E and the State and Zip Code are in column F.  

Creating the State and Zip Code Fields 

  • To separate the state and Zip Code, select the data in Column F and choose Text to Columns under the Data Tab.
  • On step 1 of the Wizard, make sure that Delimited is selected then click Next.
  • On step 2 of the Wizard, click the Space delimiter and click the box for Treat consecutive delimiters as one. This will eliminate the creation of a blank column between State and Zip Code since there are two spaces between state and Zip Code. Click Next to continue.
  • On step 3 of the Wizard, click Do not import column (skip) for the first blank column as shown in Figure 12. The column with the state can be left as General or Text. Make the Zip Code column Text and click Finish.
  • Resize and modify the column names for columns E, F, and G.
  • The final data are shown in Figure 5: 

Figure 5

 

 

 Comments

 
To add a comment, Sign In
Total Comments: 14
 
Erin posted on 7/10/2013 11:36 AM
The text-to-columns function in Excel is one of its best features! I have used it on innumerable occasions for just these types of issues but I never new about the "Treat consecutive delimiters as one" box. Thanks for the tip!
Reuben posted on 7/10/2013 11:42 AM
This is a fantastic feature. Usually I use some other program to write complicated instructions to separate text in this way, but this seems a bit easier. Can't wait to try it out!
Jim posted on 7/10/2013 11:59 AM
Thanks--the "Treat consecutive delimiters as one" feature is good to know about! It's useful to remember that almost anything can be used as a delimiter. I use text-to-columns frequently to clean up poorly-formatted text that I've downloaded from the Web (ex. "Whatsmatta U, Anytown MN"). The alternative to using "delimiters" in the wizard is the "fixed width" option. This comes in very handy when trying to make sense of some imported data tables (Census, BLS, etc.) The "fixed width" option is also useful for making sense of data delivered as a string "0100301300&etc", where cols 1-5 are an ID number, gender is stored in col. 6, etc.
Cal posted on 7/10/2013 12:21 PM
I agree with Erin and Jim. I had not ever used the "Treat consecutive..." and at times this has been frustrating. This is an excellent tip - both in content and in clarity!
Erin posted on 7/10/2013 12:30 PM
I'm yet another constant user of text-to-columns who somehow missed the "consecutive delimiters" functionality! Thanks for the great tip!
Nicole posted on 7/10/2013 1:01 PM
Text to columns is such a time saver. What a great tip to share!
Terry posted on 7/10/2013 1:15 PM
Text-to-column has always been a real time saver. I've always told people that a lot of my job consists of cleaning data.
Pallabi posted on 7/10/2013 2:01 PM
I find the 'Text to Columns' feature of excel very useful. Other than parsing names, zip codes, addresses, we use this tool to separate letters from numbers e.g. IDs in our system start with a 'P' followed by numbers, but most of the time we need just the number part. Thanks for sharing the tip.
Michelle posted on 7/10/2013 9:49 PM
Thanks for the tips and clear directions! I will be sharing this with my faculty who are looking for Excel pointers so they can work with their department's data more efficiently and effectively.
Evelina posted on 7/11/2013 8:49 AM
"Treat consecutive delimiters as one" - what a great tip! I use "Text to Columns" a lot and this new tip will make it so much more efficient! Thank you!
Gary posted on 7/11/2013 1:22 PM
Concise and useful - nice tip. I use this feature to clean-up and Windows-ize fixed-width output from R for email distribution.
Sue posted on 7/15/2013 7:50 AM
This is a very useful tip for dealing with data that is not always laid out in the best manner. I especially like the example of separating the name, because prefixes, suffixes, and last names with spaces can cause trouble when splitting the data. This is a very valuable tip. I concur with others - the "treat delimiters as one" option is handy to learn about!
Eric posted on 7/15/2013 11:53 AM
Thank you for sharing this, Mary Jo! Text-to-columns is one of my favorite features. I have found it to be very useful, for example, when I want to split a column that contains course names into a new column for only the course prefix.
Kendra posted on 7/25/2013 5:07 PM
I love and use this feature constantly! I also use the "concatenate" feature in some instances as I need to separate and reorganize.