<$BlogRSDURL$>

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.

=SUBSTITUTE(D3,LEFT(D3,4),"")

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

=VALUE(SUBSTITUTE(D3,LEFT(D3,4),""))

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



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

Comments: Post a Comment

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