4

I have some cells labeled as euros and some as dollars.

For some reason the dollar sign is left-justified with a space between it and the value.

The cell is right justified.

How can I get the dollar-formatted currency cell to appear correctly, i.e. on right without the space between dollar sign and value?

enter image description here

1
  • 1
    The answer depends on if the underlying value in the cell is a number with cell formatting doing the presentation? Or is the cell value a string as shown in your image? – chris neilsen Oct 7 '11 at 23:05
2

Are you using the "currency" cell format, or the "accounting" cell format? I've found that "accounting" puts the dollar sign way over to the left, while "currency" puts it against the numbers. I've also found that clicking the dollar sign in the toolbar applies "accounting" which is never what I want because I'm not an accountant.

Looks like when choosing the euro symbol you get the choice of putting it before or after the number, and you chose to put it after. If you chose to put it before, you'd get the same display with the symbol way over to the left.

1

I'm sure people might still be Googling for this answer, I did.

To format foreign currency out of Accounting:

Click cell -> select currency type from the Ribbon (automatically puts it in accounting format) -> Right click on cell -> Format Cells -> Number Tab -> Currency -> Symbol -> Click OK -> The "¥_____1,000" (with spaces) will now appear as "¥1,000"

Trying to select a foreign currency from the ribbon changes the cell to accounting, trying to change it back to currency changes it back to USD. This will fix that problem and make the cell summable, which is not if you use the alt code (alt 157) to type ¥.

If it's not summing, retype number and repeat the process.

0

This question has been asked elsewhere and the answer has generally required some vba code.

Option Explicit 

Public Function SUMCURRENCY(rgSumArea As Range) As Long 
    Dim rgCell As Range 

    For Each rgCell In rgSumArea 
        SUMCURRENCY = SUMCURRENCY + Strings.Mid(rgCell.Value, 2) 
    Next rgCell 
End Function 

Or you could play around with the Custom cell format. Here's instructions on the syntax necessary for custom formatting. A problem with this solution would be if you have multiple forms of currency in a column or row.

0

I hope I understood your question right.

You can try this:

  1. Go to Cell Styles.
  2. Right-click Currency. Click Modify.
  3. Click Format.
  4. Click Custom and then enter the format:

    #,##0.00 $

  5. Click OK.

Whenever you click the Currency format button on the toolbar, it should use the format/style you set up.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.