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
There are no comments.