Use Formats to identify currency??

G12

Board Regular
Joined
Nov 11, 2008
Messages
113
Office Version
  1. 365
Platform
  1. Windows
Hello. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
So, for example I have a column of figures that contain:<o:p></o:p>
£100.00<o:p></o:p>
100.00 EUR<o:p></o:p>
$100.00<o:p></o:p>
100.00 NOK<o:p></o:p>
<o:p></o:p>
Obviously, that means the cell value is 100 in each case. <o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
I am trying to convert all into $ so I need to be able to show what they are. <o:p></o:p>
<o:p></o:p>
I am using Excel 2000 and all help is much appreciated. <o:p></o:p>


Many thanks

G12
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

jbvinny

Active Member
Joined
Nov 21, 2007
Messages
274
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

Board Regular
Joined
Nov 11, 2008
Messages
113
Office Version
  1. 365
Platform
  1. Windows
Hi and thanks for the reply. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
I wondered if this function is available on 2000? <o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
I have looked through a number of previous threads looking at similar results <o:p></o:p>
<o:p></o:p>
http://www.mrexcel.com/forum/showthread.php?t=327207<o:p></o:p>
<o:p></o:p>
http://www.mrexcel.com/forum/showthread.php?t=341681<o:p></o:p>
<o:p></o:p>
http://www.mrexcel.com/forum/showthread.php?t=362903<o:p></o:p>
<o:p></o:p>
The only thing I have had any success with was using the following<o:p></o:p>
<o:p></o:p>
=RIGHT(GET.CELL(53,WORKSHEET!M5),3)<o:p></o:p>
<o:p></o:p>
Where you define a cell name then in the “refers to” type the above. <o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
Please let me know if you can shed any more light on the problem
<o:p></o:p>
Thanks in advance
<o:p></o:p>
G12
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Board Regular
Joined
Nov 11, 2008
Messages
113
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,156
Latest member
WDMix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top