Adding $ format to text string, without rounding.

Jeffrey Green

Well-known Member
Joined
Oct 24, 2007
Messages
1,021
i have data that comes to me as
12345
1234567
12345678, etc.

Those need to $, 2 decimals, so they should be:

$123.45
$12345.67
$123456.78

(I can live without the $ sign)

When i use Custom format $#.##,
the numbers round up. 12345 becomes $123.46, etc.

I would like to use a custom format, but i am not against a macro.

Thanks everyone, be safe.

Jeff
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Place 100 in any empty cell, then select this cell and do a Copy.
Then, select all your data and go to Paste Special, and choose the "Divide" option.
This wil divide the whole list by 100.
Then you can apply the custom format you mentioned.
 
Upvote 0
Thanks Joe, but I maybe 10 columns in a large spreadsheet to do this to. I will give that a shot, but would prefer something a little easier to allow the new folks to just get the cells formatted . . .
 
Upvote 0
When i use Custom format $#.##,
the numbers round up. 12345 becomes $123.46, etc.
I don't see how that could possibly happen. That format would not even change 12345 into 123.45 never mind perform any rounding.
 
Upvote 0
Keep in mind that changing the format does NOT change the actual value.
So even if you use some custom format to show $123.45, Excel will still use 12345 in any calculation.

If you want to change the value, then you either have to use a method like I showed you, or use a formula, or VBA.

We can use VBA to do what I showed you automatically. The following will run on whatever range they have selected before they run this macro:
VBA Code:
Sub MyFormat()

    Dim rng As Range
    
    Application.ScreenUpdating = False
    
'   Set current selection equal to range variable
    Set rng = Selection
    
    Range("AZ1").FormulaR1C1 = "100"
    Range("E1").Select
    Range("AZ1").Copy
    rng.PasteSpecial Paste:=xlPasteAll, Operation:=xlDivide, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    rng.NumberFormat = "$#.00"
    
    Range("AZ1").ClearContents
    
    Application.ScreenUpdating = True
    
End Sub

Note: Also notice Rory's comments. I am not sure exactly what you want to happen there.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

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
Back
Top