Parsing Data

Problem

You've imported data from a text file as discussed in Recipe 3.3, but all data on each row is lumped into a single cell. You'd like to separate the data on each row into columns.

Solution

Select the data you'd like to parse. Then select Data "Text to Columns... " from the main menu bar to open the "Convert Text to Columns Wizard.

Figure 3-12. Data imported using web query

 

Discussion

The data shown in Figure 3-7 (in Recipe 3.3) was imported from a text file via drag-and-drop. The problem with this approach is that all the data on each row is lumped into a single cell. This is less of a problem with the import method in Recipe 3.1, because using File images/U2192.jpg border=0> Open... automatically launches the Text Import Wizard, which allows you to set columns for your data either manually or by specifying delimiters.

The "Convert Text to Columns" Wizard, accessible via Data "Text to Columns ...," is essentially the Text Import Wizard. The three steps in the "Convert Text to Columns Wizard are identical to those in the Text Import Wizard. First you specify whether the text to be parsed is in fixed-width form or delimited. Then, depending on your selection in step 1, you either manually set column breaks or select the delimiting character. Finally, you can specify data formats and choose which columns to ignore.

Performing these steps on the data shown in Figure 3-7 yields the parsed data shown in Figure 3-13.

Figure 3-13. Parsed data


Using Excel

Getting Acquainted with Visual Basic for Applications

Collecting and Cleaning Up Data

Charting

Statistical Analysis

Time Series Analysis

Mathematical Functions

Curve Fitting and Regression

Solving Equations

Numerical Integration and Differentiation

Solving Ordinary Differential Equations

Solving Partial Differential Equations

Performing Optimization Analyses in Excel

Introduction to Financial Calculations

Index



Excel Scientific and Engineering Cookbook
Excel Scientific and Engineering Cookbook (Cookbooks (OReilly))
ISBN: 0596008791
EAN: 2147483647
Year: N/A
Pages: 206
Authors: David M Bourg

Similar book on Amazon

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