I received an Excel file containing an extract of sales forecasts. There is no column explicitly defining currency unit. Currency unit is only shown by number formatting (GBP, EUR or CHF). Does anyone know how can I detect the currency format in order to apply appropriate exchange rate for the different currencies?
Example:
Displayed Number Format / Actual Number in Cell
200,000 GBP / 200000
10,000 CHF / 10000
500,000 EUR / 500000
Many thanks, Tym
Try the following User Defined Function:
Public Function txet(r As Range) As String
Dim s As String
s = Replace(r.NumberFormat, Chr(34), "")
txet = ""
If Len(s) < 3 Then Exit Function
s = Right(s, 3)
txet = s
End Function
User Defined Functions (UDFs) are very easy to install and use:
If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx
To remove the UDF:
To use the UDF from Excel:
=txet(A1)
To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
and
http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx
and for specifics on UDFs, see:
http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
Macros must be enabled for this to work!
You can do this in VBA.
Sub CheckFormat()
Dim myCell, j, LastRow
Worksheets("Sheet1").Activate
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For j = 1 To LastRow
Set myCell = Cells(j, "A")
Debug.Print myCell.NumberFormat
Next j
End Sub
I put your samples in A1, A2, and A3. The above code outputs this:
[$GBP] #,##0.00
[$CHF] #,##0.00
[$EUR] #,##0.00
You can then parse the first parts of that to decide what conversion rate to apply.
