Currency Fomat

[cameronb]

I have tried to create a custom format for currency to no evail. I am trying to format a range so that any would be displayed as:

GBP100,000

or

USD20,000


Can anyone assist as I cant seem to be able to achieve this in custom formats?

 

[ScottR]

Something like this

"GBP" #,##0;"GBP" -#,##0

 

[lenze]

Do you just mean
"GBP" #

"USD" #

lenze

 

[cameronb]

The amount and currency are both stored in separate columns. The currencies range from USD to MXN. When I try to concatinate, the comma separation disappears. I have tried to create a custom number format which will allow for the first three characters of the number to be letters and treat the remainder as a number but no joy.

 

[Richard Schollar]

Hi Cameron

Do you mean something like this:

Excel Workbook
	L	M	N
3	USD	1000	USD 1,000.00
4	GBP	87.54	GBP 87.54
Sheet4

Excel 2002

Cell Formulas
Range	Formula
N3	=TEXT(M3,"""" &L3&""" #,##0.00")
N4	=TEXT(M4,"""" &L4&""" #,##0.00")

 

[lenze]

Maybe a different approach
http://www.mrexcel.com/forum/showpost.php?p=1874558&postcount=7

lenze

 

[cameronb]

Richard, that is exactly what I am after, many thanks. Not sure I can put this into VBA, but will find a way round regardless. Many thanks, I have been pulling my hair out with this one!!!!!

Lenze, I will check out the VBA with which you have used cases to get the formatting correct. Thanks