Transforming Data With Formulas
This tip describes a technique that should be in the arsenal of every Excel user. It describes how to use formulas to transform data.
The figure below shows a simple example. The text in column A consists of lower case letters. The goal is to transform these cells so they display "proper" case. This will be done by creating formulas that use Excel's PROPER function.
The steps below are specific to this example. But they can easily be adapted to other types of data transformations.
Creating the formulas
In this case, the formulas will go in column D. As you'll see, this is just a temporary location. The formula results will eventually replace the names in column A.
Copying and pasting the formula cells
In this step, the formula cells are copied, and pasted as values -- overwriting the original data in column A.
At this point, the worksheet looks like this:
Deleting the temporary formulas
The formulas in column D are no longer necessary, so you can delete them.