Adding $ format to text string, without rounding.

Jeffrey Green

Well-known Member
Joined
Oct 24, 2007
Messages
1,019
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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,045
Office Version
  1. 365
Platform
  1. Windows
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.
 

Jeffrey Green

Well-known Member
Joined
Oct 24, 2007
Messages
1,019
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 . . .
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,157
Office Version
  1. 365
Platform
  1. Windows
Try this custom format.

$#\.00
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,769
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,045
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,421
Messages
5,642,018
Members
417,250
Latest member
spr1nger

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