There is an easy way to extract East, North, and South sales (and data similar to this example) without using text functions. Simply select cells A3:A6, and then on the Data tab on the rib-bon, in the Data Tools group, click Text To Columns. Select Delimited, click Next, and then fill in the dialog box as shown in Figure 6-5.
FIGURE 6-5 Convert Text To Columns Wizard.
Entering the plus sign in the Delimiters area directs Excel to separate each cell into columns, breaking at each occurrence of the plus sign. Note that options are provided for breaking data at tabs, semicolons, commas, or spaces. Now click Next, select the upper-left corner of your destination range (in the example, I chose cell A8), and click Finish. The result is shown in Figure 6-6.
FIGURE 6-6 Results of Convert Text To Columns Wizard.
At the end of each school semester, my students evaluate my teaching performance on a scale from 1 to 7 . I know how many students gave me each possible rating score . How can I easily create a bar graph of my teaching evaluation scores?
The file Repeatedhisto.xlsx contains my teaching evaluation scores (on a scale from 1 through 7). Two people gave me scores of 1, three people gave me scores of 2, and so on. Using the REPT function, you can easily create a graph to summarize this data. Simply copy from D4 to D5:D10 the formula =REPT(“|”,C4). This formula places in column D as many “|” characters as the entry in column C. Figure 6-7 makes clear the preponderance of good scores (6s and 7s) and the relative rarity of poor scores (1s and 2s). Repeating a character such as | enables you to easily mimic a bar graph or histogram. See Chapter 41, “Summarizing Data by Using Histograms,” for further discussion of how to create histograms with Excel.
Chapter 6 Text Functions 47
FIGURE 6-7 Using the REPT function to create a frequency graph.
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 on 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, you need to follow the first letter of the employee’s first name by the employee’s 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 combina-tions (lineups). (Lineup 1 played 10.4 minutes, and so on.) Use text funccombina-tions 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 MBA 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 a machine purchase. Determine the year of each purchase.
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 Equivalents 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 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 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, 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 replace only the third occurrence of a 1 in cell A4 with a 2.
16. The file Problem16data.xlsx contains the number of people who responded 1–5 on a marketing questionnaire (1 = Very unlikely to buy product, …, 5 = Very likely to buy product). Summarize this data graphically by using the asterisk symbol. To make your summary look more appealing, you can go to the Home tab on the ribbon and choose Orientation in the Alignment group and make the text vertical. Then right-click the row number, and increase the row height. Finally, in the Alignment group choose Wrap Text so that you make the text of your graph vertical.
17. The file Problem17data.xlsx contains people’s names (such as Mr. John Doe). Use text functions to extract each person’s title and first name to separate columns.
49