Excel – Use the Text Functions

This tip is provided by Diane Ebersole with the State Bar of Michigan's Practice Management Resource Center.

Do you ever do a merge using an Excel spreadsheet with a Word document?

Does the resulting number formatting make you crazy? Here is a possible solution:

One frequently overlooked function (equation) family residing in Excel is the group of functions under the Text category. The Dollar function in this group will take the contents of a cell containing either a number or a formula and turn the results into text with dollar formatting. This is a really valuable tool if the ultimate result is used in a merge to create a document such as a bill.

To use this function:
1. Click in the cell where you want to enter the formula.

2. Click on the fx on the Formula Bar. (If the Formula Bar is not visible, click on View and turn it on.)

3. In the Select Category Box select Text.

4. In the Select a Function Box select Dollar.

5. The Function Arguments Box will display: In the number box enter the cell number for the source number (example C2)." In the decimals box enter the number of decimal places to display (example 2).

6. Click OK.

7. Hit the enter key to see the results of the function.

8. The contents of the cell will be displayed left justified because that is the default setting for text. Using the copy and Paste Special – Formula capabilities of Excel will let you put this formula wherever it is needed in the spreadsheet.

The beauty of this function is that it transforms the number formatted contents into text which will be inserted into your merge document correctly. If this description has left you dazed and confused look at http://office.microsoft.com/en-us/excel-help/dollar-function-HP010062563.aspx for another description.