Extracting Data by Using the Text To Columns Wizard


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 of the Ribbon, click Text to Columns. Then select Delimited, click Next, and fill in the dialog box as shown in Figure 5-5 on the next page.

image from book
Figure 5-5: Text To Columns Wizard dialog box

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 there are options given for breaking data at tabs, semicolons, commas, or spaces. Now click Next, select the upper-left corner of your destination range (we chose cell A8), and click Finish. The result is shown in Figure 5-6.

image from book
Figure 5-6: Result of 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 we 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 "|" as the entry in column C. Figure 5-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 us to easily mimic a bar graph or histogram. See Chapter 36, “Summarizing Data with Histograms,” for further discussion of how to create histograms with Excel.

    image from book
    Figure 5-7: Using the REPT function to create a frequency graph.




Microsoft Press - Microsoft Office Excel 2007. Data Analysis and Business Modeling
MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)
ISBN: 0735623961
EAN: 2147483647
Year: 2007
Pages: 200

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net