Prepare Data for Excel: Make Information Spreadsheet-Readable
If you feed correctly formatted data to Excel, it can handle a multitude of calculations flawlessly. But feed it a piece of "indigestible" information--that is, data not formatted to its liking--and the calculations will generate error messages that looks like this: Fortunately, Excel contains a group of functions designed to quickly and easily transform or retrieve many different kinds of otherwise incompatible information so the spreadsheet can perform its calculations flawlessly. In a typical situation a CPA is preparing a fixed-asset report and the only data available are a code that contains all the necessary information for the report--the location of the asset, its description and the year of purchase. This information can be extracted manually, or an Excel tool can reach into the code and nimbly extract and reshape just what it needs. The functions we'll be demonstrating are CONCATENATE, RIGHT, LEFT, MID, ROUND, TRIM and LEN. Once you see them in operation, you'll surely include them in your cache of useful Excel tools. STRING THEM TOGETHER We'll begin with CONCATENATE. The word means to connect or link in a series. Say you have a spreadsheet listing a company's employees, with first names in one cell, last names in an adjacent cell and titles in a third, as in: But your report needs all that information tucked into a single cell, with a hyphen inserted between each person's title and name, such as CEO-Rose Smith. To create it, place the following formula in the cell where you want the answer displayed (E2): =CONCATENATE(D2,"-",B2," ",C2). The formula tells Excel to place the information in this order: D2 first, then a hyphen, then B2, a space and finally C2. Added characters (hyphen, space) are enclosed in double quotes and elements are separated by commas. The result resembles exhibit 1 at right. [ILLUSTRATION OMITTED] KEEP IT RIGHT The RIGHT function is used to extract selected characters from a cell. For our example we'll use a CPA preparing a fixed-asset report for a batch of laptops. All the necessary data are embedded in codes, a variation of which looks like this: NY LAPTOP 2003. The first two letters (NY) show the location of the asset, the next six (LAPTOP) describe the asset and the final four (2003) show its year of purchase. Exhibit 2, page 77, shows what one code in a spreadsheet looks like. [ILLUSTRATION OMITTED] We'll begin by extracting the year of purchase. Since the year is embedded in the right end of the code, we'll use the RIGHT function, and write a formula in the cell where we want the answer to appear (C2). The formula =RIGHT(B2,4) tells Excel to extract four characters (2003) from the right side of cell B2, creating a spreadsheet that resembles exhibit 3, below. [ILLUSTRATION OMITTED] To extract the computer location (NY) from the leftmost side of the code, we'll use the LEFT function and write this formula into cell E2: =LEFT(B2,2). Again, the formula tells Excel to look into cell B2 and extract the first two characters (NY). The result will look like exhibit 4, below. [ILLUSTRATION OMITTED] To extract the identity of the asset (LAPTOP) that's embedded in the middle of the code, we'll use the MID function. But sometimes just saying it's in the middle of the code is not enough information for Excel. So we add further hints, such as this: The target code contains six characters and begins with the fourth character in the code. Putting all those hints together, we get this formula: [ILLUSTRATION OMITTED] The target begins with the fourth character (L) and is six characters long. The result will resemble exhibit 5, below. [ILLUSTRATION OMITTED] ROUND IT OFF Sometimes it's more important to present information in ways that make data easy to grasp and retain than to give all the exacting detail. …