Barbara asks you to organize the data for sportswear retailers into an Excel table. The data is currently stored in a text file and is organized by state. You will begin by creating an Excel workbook and then pasting the data from the text file into a worksheet.
Complete the following:
1. Use Notepad (or another text editor) to open the text file named Sportswr.txt in the Chapter 7 folder and examine the data. Close the file, and then close Notepad.
2. Create a new workbook named Sportswear Retailers.xlsx and save the file in the Chapter 7 folder. Import the data from the text file into a worksheet named
Imported Data, starting in cell A1.
3. Create a list of comma-separated values from the data you pasted by concatenating the company name, street address, city, state, ZIP code, and phone number. Separate the name, street address, city, state, ZIP code, and phone number information with commas. Trim each text string in the concatenated value to remove all spaces except for spaces between words.
4. Use the appropriate functions to identify the rows of data that hold the concatenated values in the correct order.
5. Copy and paste the concatenated information as a list of comma-separated value into a new worksheet named CSV in the Sportswear Retailers.xlsx workbook. Delete all invalid rows and columns that do not contain the comma-separated values.
6. Convert the comma-separated values into a structured list with the data for the company, street address, city, state, ZIP code, and phone number appearing in different cells of the worksheet. (Hint: Run the Convert Text to Columns Wizard twice to convert all of the data, being careful not to overwrite any existing data in the process. You can use this process to separate the state from the ZIP code. Be certain to remove any extra spaces remaining at the beginning or end of the data.)
7. Correct any errors in the data that were introduced during the conversion.
8. Add a header row with labels to describe the data in each column.
9. Change the data in the worksheet to an Excel table.
10. Sort the data by state, then by city, and then by company in ascending order.
11. Filter the results to display only those companies in Oregon and show the total number of companies in the state.
12. Save and close the Sportswear Retailers.xlsx workbook.
Login | Sign Up
