June 16, 2004

The TEXT function

When is a number not a number? When it becomes text.

Let me explain, although you can do an similar thing with the Format Cells dialog box, formatting by use of the TEXT function enables to you choose formatting under certain conditions.

Suppose you have copied some monetary amounts from another Worksheet or Workbook. You can copy the values easily enough, but because you are using different currencies for certain customers, you may find yourself having problems in retaining the original format Although there are other ways to avoid this, using the TEXT function must be one of the more easier choices.

As an example, look at the picture below.

I have customers listed in column B:B and some amounts in column C:C. How did I change these amounts to Euro for Company ABC and US Dollars for Company XYZ in column D:D?

Let's look at the formats first. For Euro I have used [$EUR] #,##0.00, and for US Dollars I have used [$USD] #,##0.00. (Notice I have used square brackets and $ signs for EUR and USD for an Accounting format, whereas with the Format Cells dialog box, I could use either this format or just use inverted commas as in "EUR" #,##0.00 or "USD" #,##0.00. Either will do the job, although there may be some slight differences in spacing)

The TEXT formula is written as =TEXT(value, format_text), so for cell D3 I want to reference C3 which shows the amount or value, then I write the format I want the amount to change to, EUR or USD.

There are only 2 companies invloved in my example so a simple IF function is enough to decide between the EUR and USD formats, the company names in cell B3 being the deciding factor

=IF(B3="Company ABC",TEXT(C3,"[$EUR] #,##0.00"),TEXT(C3,"[$USD] #,##0.00"))

This formula will usually work, but if I have blank cells in column B, the above formula will automatically determine the format must be for US Dollars, so as a precaution I'll add an extra condition to be on the safe side.

=IF(B3="Company ABC",TEXT(C3,"[$EUR] #,##0.00"),IF(B3="Company XYZ",TEXT(C3,"[$USD] #,##0.00"),""))

The formula is now a nested IF formula which determines that if the customer is Company ABC to format in Euro, otherwise if the customer is Company XYZ to format in US Dollars, otherwise the two inverted comas shown as "" at he end of the formula will just leave a blank cell.

Next time I'll have a look at the "opposite" of the TEXT function...the VALUE function.

Comments: Post a Comment

This page is powered by Blogger. Isn't yours?