Steps To Success: Level 1 is currently stored in a text file and is organized by state. You will begin by creating an Barbara asks you to organize the data for sportswear retailers into an Excel table. The data 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 Al. 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. Chapter 7 Organizing Data for Effective Analysis 5. Copy and paste the concatenated information as a list of comma-separated values 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. U V 6 H K M N O R S Subset of imported Sportswr data results (after completing steps to Success steps 1 through 5): 1 Underground Sports Underground Sports,226 South La Brea Avenue Sunnyvale, CA 92824,909-498-2009 2 226 South La Brea Avenue 226 South La Brea Avenue, Sunnyvale, CA 92824,909.498-2019,Churchill's Sportswear 3. Sunnyvale, CA 92824 Sunnyvale, CA 92824,909-498-2019,Churchill's Sportswear,2426 South Main Street 4 909-498-2089 909-498-2089.Churchill's Sportswear,2426 South Main Street, Redondo Beach, CA 90220 5 Churchill's Sportswear Churchill's Sportswear,2426 South Main Street, Redondo Beach, CA 90220,860-942-2626 2426 South Main Street 2426 South Main Street, Redondo Beach, CA 90220,860-942-2626 Summer Times Clothiers 7 Redondo Beach, CA 90220 Redondo Beach, CA 90220,860-942-2626, Summer Times Clothiers, 40820 Winchester Road 860-942-2626 860-942-2626,Summer Times Clothiers, 40320 Winchester Road, Mission Viejo, CA 94284 9 Summer Times Clothiers Summer Times Clothiers 40820 Winchester Road, Mission Viejo, CA 94284,640 896-4264 10 40820 Winchester Road 40920 Winchester Road, Mission Viejo, CA 94284,640-896-4264, Wiseguys Industries 11 Mission Viejo, CA 94284 Mission Viejo, CA 94284,640-896-4264, Wiseguys Industries, 244 East 2st Street 12 640-896-4264 640-896-4264, Wiseguys Industries, 244 East2st Street, Garden Grove, CA 94920 13 Wiseguys Industries Wiseguys Industries 244 East 2st Street Garden Grove, CA 94920,420-842-8244 14 244 East 2st Street 244 East 2st Street Garden Grove, CA 91920,420-842-8244 Sport Shoe Shop 15 Garden Grove, CA 94920 Garden Grove, CA 94920,420 842 8244 Sport Shoe Shop, 2644 Broadway Street 16 420-842-8244 420-842-8244 Sport Shoe Shop, 2644 Broadway Street, Petaluma, CA 92424 17 Sport Shoe Shop Sport Shoe Shop,2644 Broadway Street, Petaluma, CA 92424,629-606-9092 18 2644 Broadway Street 2644 Broadway Street Petaluma, CA 92424,629-696-9092, latest and Greatest 19 Petaluma, CA 92424 Petaluma, CA 92424,629-696-9092, Latest and Greatest,220 East 9th Street 20 629-696-9092 629-696-9092, Latest and Greatest,220 East 9th Street, Los Angeles, CA 94628 Latest and Greatest Latest and Greatest,220 East 3th Street Los Angeles, CA 94628,206-242.4869 -2089 swear treet 90220 -2626 hiers Road 94284 -4264 tries 8 D 1 Incorrect Incorrect Incorrect 1 Incorrect Incorrect Incorrect 1 incorrect Incorrect Incorrect 1 Incorrect Incorrect Incorrect 1 Incorrect Incorrect Incorrect treet 94920 -8244 Shop treet 92424 9092 atest treet 94628 -4869
