Custom number format and currency converions

[schult]

I have a large file downloaded from SAP business warehouse and all the value are formatted in different curencies. For Example

3,705.00 EUR shows up in the cell, but that is because it has a custom cell format of #,##0.00 "EUR"
or
247,028,600.00 IDR , cell format is #,##0.00 "IDR"

I need to be able to read the "EUR" or "IDR", etc from the cell format so that I can then do a currency conversion on the numbers?

Does anyone know a way of doing this>

[DonkeyOte]

You will need to use VBA... is that Viable ?

An example UDF based on your formatting (to be placed in a new Module):

Please Login or Register  to view this content.

Called from a cell such that

B1: =FXCONVERT(A1)
where A1 holds value with Custom Format

When saving file in XL2007 you need to save as .xlsm ... also ensure Security settings are such that Macros will be enabled when re-opened.

Better wherever possible to store the currency code in a separate column adjacent to the values thereby negating the need for any VBA.

[EllBol]

Assuming you value is in cell B1, try using the formula:

=RIGHT(B1,LEN(B1)-SEARCH(" ",B1))

EllBol.

[schult]

Perfect, thank you.

I modified it so that I jsut returned the text of the currency, then I use a vlookup to get the conversion, I have ~35 currencies to work with and need to update it based on the latest monthly numbers.

The updated function is:

Function FXCONVERT(rngValue As Range) As String
FXCONVERT = Right(rngValue.Text, 3)
End Function

[DonkeyOte]

Just as a heads up, without knowing the finer details of your plan you may find you could do the conversion within the Function itself, eg:

Please Login or Register  to view this content.

So the above would lookup the Currency Code on Sheet RATES (A:B) and divide the underlying value by the FX rate... if the Rate were not found it would return a text string.

[schult]

Good to know, a much more elegant solution.

I know enought VBA to be dangerous, but not enough to be efficient.