Problems

1. Cells B2:B5 of the workbook Showbiz.xlsx contain the fictitious addresses of some of our favorite people. Use text functions to extract each person’s name to one column and each person’s street address to another.

2. The workbook IDprice.xlsx contains the product ID and prices for various products. Use text functions to put the product IDs and prices in separate columns. Then use the Text To Columns command on the Data tab of the Ribbon to accomplish the same goal.

3. The workbook Quarterlygnpdata.xlsx contains quarterly GNP data for the United States (in billions of 1996 dollars). Extract this data to three separate columns, where the first column contains the year, the second column contains the quarter number, and the third column contains the GNP value.

4. The file Textstylesdata.xlsx contains information about the style, color, and size for a variety of shirts. For example, the first shirt is style 100 (indicated by digits between the colon and the hyphen). Its color is 65, and its size is L. Use text functions to extract the style, color, and size of each shirt.

5. The file Emailproblem.xlsx gives first and last names of several new Microsoft employees. To create an e-mail address for each employee, we follow the first letter of their first name by their last name and add @microsoft.com to the end. Use text functions to efficiently create the e-mail addresses.

6. The file Lineupdata.xlsx gives the number of minutes played by five player combinations (lineups). (Lineup 1 played 10.4 minutes, and so on.) Use text functions to put this data into a form suitable for numerical calculations; for example, transform 10.4m into the number 10.4.

7. The file Reversenames.xlsx gives the first names, middle names or initials, and last names of several people. Transform these names so that the last name appears first, followed by a comma, followed by the first and middle names. For example, transform Gregory William Winston into Winston, Gregory William.

8. The file Incomefrequency.xlsx contains the distribution of starting salaries for M.B.A. graduates of Faber College. Summarize this data by creating a frequency graph.

9. Recall that CHAR(65) yields the letter A, CHAR(66) yields the letter B, and so on. Use these facts to efficiently populate cells B1:B26 with the sequence A, B, C, and so on through Z.

10. The file Capitalizefirstletter.xlsx contains various song titles or phrases such as “The rain in Spain falls mainly in the plain.” Ensure that the first letter of each song title is capitalized.

11. The file Ageofmachine.xlsx contains data in the following form:

    • S/N: 160768, vib roller,84" smooth drum,canopy Auction: 6/2–4/2005 in Montgomery, Alabama

    Each row refers to the purchase of a machine. Determine the year that each machine was purchased.

12. When downloading corporate data from the Security and Exchange Commission’s EDGAR site, you often obtain data for a company that looks something like this:

    • Cash and Cash Equivalents $31,848 $ 31,881

    How would you efficiently extract the Cash and Cash Equivalent for each company?

13. The file Lookuptwocolumns.xlsx gives the model, year, and price for each of a series of cars. Set up formulas that enable you to enter the model and year of a car, and return its car price.

14. The file Moviedata.xlsx contains the names of several movies followed by the number of copies of the movie DVD purchased by a local video store. Extract the title of each movie from this data.

15. The file Moviedata.xlsx contains the names of several movies followed by the number of copies of the movie DVD purchased by a local video store. For each movie extract the number of copies purchased from this data. Hint: You will probably want to use the SUBSTITUTE function. The syntax of the Substitute function is SUBSTITUTE(text,old_text, new_text,[instance_num]). If instance num is omitted, then every occurrence of old text in text is replaced by new text. If instance num is given, then only that occurrence of old text is replaced by new text. For example, SUBSTITUTE(A4,1,2) would replace each 1 in cell A4 with a 2, but SUBSTITUTE(A4,1,2,3) would only replace the third occurrence of a 1 in cell A4 with a 2.