Decomposition and Reconstruction of Complex Spreadsheet Functions

This paper addresses the difficulties in teaching students the creative thinking skills necessary to assemble some straightforward string manipulation functions, by themselves limited in scope, to produce new output that can be analyzed using traditional worksheet methods. The manipulation of strings is very useful in preparing data for use by either Excel or virtually any other program. In fact, Excel is an excellent tool for modifying data for importation into a database. The student is shown how to parse a block of raw data using string manipulation functions, such as the LEFT(), RIGHT(), MID(), FIND(), and SUBSTITUTE(). Critical to utilizing these functions effectively is knowing how they work and what mental models one must create to combine functions creatively to yield a proper solution. The process involved for each of these is described by means of a detailed illustration. The instructor will be able to use these tested techniques to provide students with increased mastery over the spreadsheet tool