Use Formats to identify currency??

[G12]

Hello. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have a bit of an issue where the spreadsheet I am using has a number of different currencies with no identifying means other than the formatting of the cell. <o></o>
<o></o>
So, for example I have a column of figures that contain:<o></o>
£100.00<o></o>
100.00 EUR<o></o>
$100.00<o></o>
100.00 NOK<o></o>
<o></o>
Obviously, that means the cell value is 100 in each case. <o></o>
<o></o>
Is there a function I can use which will allow me to determine currency in the cell by looking at the format of the cell? <o></o>
<o></o>
I am trying to convert all into $ so I need to be able to show what they are. <o></o>
<o></o>
I am using Excel 2000 and all help is much appreciated. <o></o>


Many thanks

G12

 

[jbvinny]

Function GetFormat(Cell As Range) As String
GetFormat = Cell.NumberFormat
End Function


Place the above code in a module within the workbook you need it for. This will return the format of a cell by enter =GetFromat(CellValue) in a cell.

 

[G12]

Hi and thanks for the reply. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am struggling make this work. I have tried making it a public function, in a personal module but also in both the sheet and workbook. In all cases the =GetFormat(Cell Ref) returns #NAME? <o></o>
<o></o>
I wondered if this function is available on 2000? <o></o>
<o></o>
I also notice that the formats of the cells are a custom format. (#,## "EUR", "$" #,## etc. I thought this may have been the problem so tried using the internal currency formats though the result still came out as #NAME? <o></o>
<o></o>
I have looked through a number of previous threads looking at similar results <o></o>
<o></o>
http://www.mrexcel.com/forum/showthread.php?t=327207<o></o>
<o></o>
http://www.mrexcel.com/forum/showthread.php?t=341681<o></o>
<o></o>
http://www.mrexcel.com/forum/showthread.php?t=362903<o></o>
<o></o>
The only thing I have had any success with was using the following<o></o>
<o></o>
=RIGHT(GET.CELL(53,WORKSHEET!M5),3)<o></o>
<o></o>
Where you define a cell name then in the “refers to” type the above. <o></o>
<o></o>
I just don't have the ability to automate it for a variable number of rows and (as you would expect) it works for the values that have the currency at the right of the Value but as the formats come out as custom formats it will not work for all. <o></o>
<o></o>
Please let me know if you can shed any more light on the problem
<o></o>
Thanks in advance
<o></o>
G12

 

[Richard Schollar]

Have you placed the function code in your Personal.xls workbook (needs to go into a standard module, not the ThisWorkbook or a worksheet module)? If so, you would need to prefix the formula in a cell with:

=Personal.xls!GetFormat(A1)

 

[G12]

Ahhh..... Got it all sorted now.

The function returned the seperate formats and I used a find and repace to replace the seperate number formats with an exchange rate which I then multiply the original number by.

To a more advance user this may seem a long way round, but I can obtain the result I am looking for and I have managed to put it all in code which takes out the pain.

Thanks to all for help

G12