Converting Text to Columns - Exercise

Contact Us or call 1-877-932-8228
Converting Text to Columns - Exercise

Converting Text to Columns

Duration: 10 to 20 minutes.

In this exercise, you will practice converting text to columns.

  1. Open Text to Columns.xlsx from your Excel2013.3/Exercises folder.
  2. In the sheet named "Presidents", convert the text in column A to three columns: first name, last name, dates in office. (No worries if your instructor already used this example in their demo - it will still be good practice!)
  3. In the sheet named "Capitols", convert the text in column A to two columns: city and state. Put the converted data into columns C and D.
  4. In the sheet named "Contact Information", convert the text in column A to five columns: name, address, city, state, and zip code. (Hint: You will need to do this in two steps.)
  5. In the sheet named "Phone Numbers", separate the area codes by converting the text in column A to three columns.

Solution:

  1. To convert the text in column A of the sheet named "Presidents" to three columns:
    1. Select column A.
    2. On the DATA tab, in the Data Tools group, click the Text to Columns command:Text to Columns Command
    3. In the Convert Text to Columns Wizard - Step 1 of 3 dialog box, select Delimited and click Next:Delimited
    4. In the Convert Text to Columns Wizard - Step 2 of 3 dialog box, check Space and click Next:Check Space
    5. In the Convert Text to Columns Wizard - Step 3 of 3 dialog box, click Finish:Wizard
  2. To convert the text in column A of the sheet named "Capitols" to two columns:
    1. Select column A.
    2. On the DATA tab, in the Data Tools group, click the Text to Columns command:Text to Columns Command
    3. In the Convert Text to Columns Wizard - Step 1 of 3 dialog box, select Delimited and click Next:Delimited
    4. In the Convert Text to Columns Wizard - Step 2 of 3 dialog box, select Comma and click Next:Select Comma
    5. In the Convert Text to Columns Wizard - Step 3 of 3 dialog box, set the Destination to cell C1 and click Finish:Set Destination
  3. To convert the text in column A of the sheet named "Contact Information" to five columns:
    1. Select column A.
    2. On the DATA tab, in the Data Tools group, click the Text to Columns command:Text to Columns Command
    3. In the Convert Text to Columns Wizard - Step 1 of 3 dialog box, select Delimited and click Next:Select Delimited
    4. In the Convert Text to Columns Wizard - Step 2 of 3 dialog box, select Comma and click Next:Select Comma
    5. In the Convert Text to Columns Wizard - Step 3 of 3 dialog box, click Finish:Click Finish
    6. Select column D.
    7. Click the Text to Columns command.
    8. In the Convert Text to Columns Wizard - Step 1 of 3 dialog box, select Delimited and click Next.
    9. In the Convert Text to Columns Wizard - Step 2 of 3 dialog box, select Space and click Next.
    10. In the Convert Text to Columns Wizard - Step 3 of 3 dialog box, click Finish.
  4. To convert the text in column A of the sheet named "Phone Numbers" to three columns:
    1. Select column A.
    2. On the DATA tab, in the Data Tools group, click the Text to Columns command:Data Tools Group
    3. In the Convert Text to Columns Wizard - Step 1 of 3 dialog box, select Delimited and click Next:Select Delimited
    4. In the Convert Text to Columns Wizard - Step 2 of 3 dialog box, select Other, enter a dash ("-"), and click Next:Select Other
    5. In the Convert Text to Columns Wizard - Step 3 of 3 dialog box, click Finish:Finish
Next