Currency vs Accounting for euro, pounds, and dollars

%3CLINGO-SUB%20id%3D%22lingo-sub-2869003%22%20slang%3D%22en-US%22%3ECurrency%20vs%20Accounting%20for%20euro%2C%20pounds%2C%20and%20dollars%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2869003%22%20slang%3D%22en-US%22%3E%3CP%3EI%20applied%20both%20currency%20and%20accounting%20formats%20to%20the%20same%20numbers%20for%20three%20different%20currencies%3A%20euro%2C%20ponds%2C%20and%20dollars.%20Here%20is%20the%20result%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22money.png%22%20style%3D%22width%3A%20234px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F318931i519ADC8AF5222FA4%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22money.png%22%20alt%3D%22money.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAs%20you%20can%20see%2C%20the%20position%20of%20euro%20symbol%20is%20different%20in%20accounting%20with%20respect%20dollars%20and%20pounds.%20Any%20idea%20why%20and%20how%20to%20fix%20that%3F%20I%20also%20want%20to%20be%20able%20to%20specify%20if%20symbol%20should%20be%20a%20prefix%20rather%20than%20a%20suffix%20%3CSTRONG%3Ewithout%3C%2FSTRONG%3E%20creating%20a%20custom%20format%2C%20so%20that%20I%20can%20easily%20apply%20it%20by%20clicking%20on%20an%20icon.%20But%20how%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2869003%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2870641%22%20slang%3D%22en-US%22%3ERe%3A%20Currency%20vs%20Accounting%20for%20euro%2C%20pounds%2C%20and%20dollars%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2870641%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1191926%22%20target%3D%22_blank%22%3E%40Dario_de_Judicibus%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fformat-numbers-as-currency-0a03bb38-1a07-458d-9e30-2b54366bc7a4%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EFormat%20numbers%20as%20currency%3C%2FA%3E%3C%2FP%3E%3CP%3ECreate%20a%20workbook%20template%20with%20specific%20currency%20formatting%20settings%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EThe%20easiest%20way%2C%20in%20my%20humble%20opinion%2C%20is%20still%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3E%3CSPAN%3Ecustom%20formatting%3A%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3Eleft%3A%20%E2%82%AC%20%23%23.%20%23%23%200%3B%20-%20%E2%82%AC%20%23.%20%23%23%200%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3Eright%3A%20%23%23.%20%23%23%20%E2%82%AC%200%3B%20-%20%23.%20%23%23%20%E2%82%AC%200%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3Eor%20as%20an%20alternative%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3E%3CSPAN%3Ewith%20VBA%3A%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EPrivate%20Sub%20currency_Change()%0AIf%20currency%20%3D%20Chr(128)%20Then%0A%20%20%20%20Range(%22A10%3AE140%22)%20%3D%200%0A%20%20%20%20ActiveSheet.Range(%22A10%3AE140%22).NumberFormat%20%3D%20%22%23%2C%23%230%20%24%22%0AElseIf%20currency%20%3D%20Chr(36)%20Then%0A%20%20%20%20Range(%22A10%3AE140%22)%20%3D%200%0A%20%20%20%20ActiveSheet.Range(%22A10%3AE140%22).NumberFormat%20%3D%20%22%23%2C%23%230%20%5B%24%24-409%5D%22%0AEnd%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolinoDE%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%20size%3D%222%22%3EWas%20the%20answer%20useful%3F%20Mark%20them%20as%20helpful!%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%20size%3D%222%22%3EThis%20will%20help%20all%20forum%20participants.%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2872027%22%20slang%3D%22en-US%22%3ERe%3A%20Currency%20vs%20Accounting%20for%20euro%2C%20pounds%2C%20and%20dollars%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2872027%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3BThat's%20in%20CURRENCY%2C%20not%20ACCOUNTING.%20I%20need%20to%20do%20that%20for%20accounting%20format.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I applied both currency and accounting formats to the same numbers for three different currencies: euro, ponds, and dollars. Here is the result:

 

money.png

As you can see, the position of euro symbol is different in accounting with respect dollars and pounds. Any idea why and how to fix that? I also want to be able to specify if symbol should be a prefix rather than a suffix without creating a custom format, so that I can easily apply it by clicking on an icon. But how?

5 Replies

@Dario_de_Judicibus 

Format numbers as currency

Create a workbook template with specific currency formatting settings

 

The easiest way, in my humble opinion, is still custom formatting:

left: € ##. ## 0; - € #. ## 0

right: ##. ## € 0; - #. ## € 0

or as an alternative with VBA:

 

Private Sub currency_Change()
If currency = Chr(128) Then
    Range("A10:E140") = 0
    ActiveSheet.Range("A10:E140").NumberFormat = "#,##0 $"
ElseIf currency = Chr(36) Then
    Range("A10:E140") = 0
    ActiveSheet.Range("A10:E140").NumberFormat = "#,##0 [$$-409]"
End If
End Sub

 

I would be happy to know if I could help.

 

NikolinoDE

I know I don't know anything (Socrates)

Was the answer useful? Mark them as helpful!

This will help all forum participants.

 

@Dario_de_Judicibus 

If you go to format number, currency, symbol, you do get a choice of position for the Euro sign.

image.png

@Peter Bartholomew That's in CURRENCY, not ACCOUNTING. I need to do that for accounting format.

@Dario_de_Judicibus 

True.  Sorry about that; I use scientific more often than currency.

From what I see, though, accounting settings also allow the placement of the Euro sign to be changed.  Perhaps not at the click of a button though.

 

image.png

Wow... you got it. Thank you very much. Now the ideal would be to associate (€ 123) to the € button in the top bar, but you gave me a good hint.