Microsoft Office Tutorials and References

In Depth Information

**Transforming Data by Using Temporary Formulas**

Transforming Data by Using Temporary Formulas

In Tip 76, I describe how to perform simple mathematical transformations on a range of numeric

data. This tip describes the much more versatile method of transforming data (numerical or text),

by using temporary formulas.

Figure 77-1 shows a worksheet with names in column A. These names are in all uppercase, and

the goal is to convert them to proper case (only the first letter of each name is uppercase).

Figure 77-1:
The goal is to transform the names in column A to proper case.

Follow these steps to transform the data in column A:

1.
Create a temporary formula in an unused column.

For this example, enter this formula in cell C2:

=PROPER(A2)

2.
Copy the formula down the column to accommodate all cells to be transformed.

3.
Select the formula cells (in column C).

4.
Press Ctrl+C.

5.
Select the original data cells (in column A).

6.
Choose Home

➜

Clipboard

➜

Paste

➜

Paste Values (V).

The original data is replaced with the transformed data (see Figure 77-2).