June 19, 2004

The VALUE function

Last time I showed how to change numbers to text. Although this is a very useful function, once a number becomes text, functions that add, count, multiply etc are no longer valid.

This is a case where the Value function comes in very handy.

In the TEXT function post below, the numbers have become text with the first four characters either "EUR " or "USD " in front (this includes the space in between the currency and the number)

In addition to converting the text to a value, we must remove these four characters which I will do with the LEFT and SUBSTITUTE functions.

=LEFT(D3,4) will give me the first four letters, "EUR " in cell D3. (Fortunately both "EUR " and "USD " have four characters we can use the same formulas in either case)

Then I use SUBSTITUTE to remove the characters like this.


It works like this, =SUBSTITUTE(original text, text to be converted, replacement text), so if the D3 entry was Money makes the world go round, then =SUBSTITUTE(D3,"Money","Love") will give me Love makes the world go round. (Note: Text must be entered with apostrophe marks, but not cell references and certain other exceptions. It is also case sensitive although there are ways around this limitation) The quotation marks "" will leave a blank instead of either "EUR " or "USD ".

EUR 500:00 in cell D3 has now become 500:00 using the above formula, but it is still text and not a value that we can use numeric formulas with.

The final part of the formula calls for the VALUE function as in my example below


Here's a pic to make the above easier to follow.

Finished and none too soon, it's way past my bedtime...G'night!

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.

June 13, 2004


I present my very first Excel game (built mostly by me, but with some useful VBA provided by Colo and also Juan Pablo González)

Play and enjoy! If you have any feedback, please leave a comment or email me at the link provided below. Thanks.

Download it here!

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