currency convertor issue

**isimpson** · 09-30-2009, 05:01 AM

hello all -
I have a spreadsheet with cells defined as Euro & Dollars. I want to add an automatic conversion so for example, if a cell is defined as $ the formula will return the sum X 4 and if it is Euro than sum X 5.
I tried to use "if" combined with "search" (so if the cell contains "$" multiply by 4) but since the cell is defined as currency the $ sign does not appear and cannot be searched…

is there a way around this or a different formula I can use?

Thanks!
Itai

**DonkeyOte** · 09-30-2009, 05:07 AM

If there is no other means by which to distinguish between currencies other than cell format then you're looking at VBA / UDF

Please Login or Register  to view this content.

The above stored in a Module in VBE (with Macros Enabled thereafter) can be utilised from a cell

C1: =CURRCONVERT(B1)

Where B1 contains your currency formatted value.

(You could also use the old XLM function GET.CELL via Names if preferred but UDF probably more straightforward)

**isimpson** · 10-01-2009, 03:38 AM

thanks for the assistance.

VB is not my strongest point... i use Excel on a very high level but only by using standard features and formulas.

seems strange to me that MS do not offer a built-in solution for this. i'm sure there are others who face the same problem.

nonetheless, i do appreciate you taking the time to reply and assist!

BR,
Itai

**DonkeyOte** · 10-01-2009, 03:46 AM

...seems strange to me that MS do not offer a built-in solution for this.

I wouldn't say so... after all a cell that contains manual input can contain multiple format attributes (ie different sections could be in bold, italic, red, black etc) so it follows that performing calculations on the basis of a cells format is inherently complex.

...i'm sure there are others who face the same problem

Yes, you will find plenty of examples of this type of UDF on the web - the key is obviously not to try and differentiate between values on the basis of cell formatting
(only possible by means of formulae if there is some other underlying logic that can be aped that is determining the format itself).

**martindwilson** · 10-01-2009, 04:00 AM

further to donkeyotes post there is xml.get.cell in the more functions addin
http://download.cnet.com/Morefunc/30...-10423159.html
used as =XLM.GET.CELL(7,A1)
it would return [$$-409]#,##0.00
for usd and
[$€-83C]#,##0.00 euro (irish)

**rwgrietveld** · 10-01-2009, 04:35 AM

You could manipulate the "format" of the cell and use that

Please Login or Register  to view this content.

where Cell format A1 = number with 2 decimals and
where Cell format A2 = $ #,##0.00;$ #,##0.00

**DonkeyOte** · 10-01-2009, 04:47 AM

I don't think that would work (in all scenarios) given it would be dependent upon client locale.

For ex. this works for you because € is default system currency thus cells formatted as Euro's return a cell format of C# (where # is determined by decimals used) whereas all other currencies return ,#, however, in the UK given £ is default system currency both Euro and USD would return ,# and only £ would return C#

Also given # you would probably want to revise the approach to check only for existence of C in format given the no. of decimals may vary.

(If the client is set to use $ as system currency then (reversed) it could work of course)