Excel Homework

Step

Instructions

Points Possible

1

Start Excel. Open the downloaded workbook named e05ch09_grader_h1_start.xlsx. Save the file with the name e05ch09_grader_h1_LastFirst, using your last and first name. If necessary, enable content.

0

2

Import the data from the query qryCustomer in the e05ch09SportsCustomers.accdb database into the CustomerDatabase worksheet in cell A2.

15

3

In cells C2:I2, type the following column headings: Clean Name, First Name, Last Name, Street Address, City, State, Full Name.

8

4

In cell C3, use the CLEAN and TRIM functions to remove any nonprintable characters and extra spaces from the name in column A. Resize the column as needed to fit the contents

10

5

In cell D3, use the RIGHT, LEN, and FIND functions to extract the first name from column C. Use the PROPER function to ensure that all first names are in the appropriate case. Resize the column as needed to fit the contents.

12

6

In cell E3, use the LEFT and FIND function to extract the last name from column C. Use the PROPER function to ensure that all last names are in the appropriate case. Resize the column as needed to fit the contents.

12

7

In cell F3, use the LEFT and FIND function to extract the street address from column B. Use the PROPER function to ensure that all street names are in the appropriate case. Resize the column as needed to fit the contents.

12

8

In cell G3, use the MID, LEN, and FIND functions to extract the city name from the address in column B. Resize the column as needed to fit the contents.

15

9

In cell H3, use the RIGHT function to extract the state from the address in column B. Resize the column as needed to fit the contents.

8

10

In cell I3, use concatenation to put the customer first name and last name together with a space in between. Resize the column as needed to fit the contents.

8

11

Save the workbook. Close the workbook. Exit Excel. Submit the workbook as directed.

0

Total Points

100