Remove decimal point from amount fields.

[TAngel]

I have a spreadsheet that contains MANY MANY rolls of dollar amounts for year end testing.
I am being told by our Software provider we need to remove all decimal points from the amounts column before we submit the file.

Example.

IS Should be
1132.47 113247
56.37 5637
89461.00 8946100

Any recomendations?

Thank you in advance for your help.
Tee

 

[carrr]

Edit > Replace > put . in the to be replaced field, leave the replace with field blank > hit Replace All

"'Tis an ill wind that blows no minds." - Malaclypse the Younger

 

[SkipVought]

Hi,

There are 2 general ways to approch this..
1) using spreadsheet functions, copy and paste
2) using vba

Method 1
1. add a new column using formula, assuming that the amount is in column A

=A1*100

and copy formula thru all rows

2. Copy the column containing your formula

3. Select the column containing the amount and Edit/Paste Special-Values

4. Delete the column containing the formula

Method 2
Past the following code in a module. Select a column containing and amount. Run this macro ONLY ONCE on each amount column!

Sub MultiplyBy100()
  With ActiveCell
    r2 = .CurrentRegion.Rows.Count
    c = .Column
  End With
  For r = 1 To r2
    With Cells(r, c)
      If IsNumeric(.Value) Then _
          .Value = .Value * 100
    End With
  Next
End Sub

Hope this helps


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[TAngel]

Thank you for your help. It worked perfectly !!
Tee

 

[SkipVought]

carr,

The replace method only works if a decimal point has been explicitly keyed in. It does NOT work with formatted decimal places.

;-)

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[carrr]

Understood. I didn't read anything explicit in their post, though, that led me to think formatting was at issue...yet.

"'Tis an ill wind that blows no minds." - Malaclypse the Younger

 

[KenWright]

Just an addendum, but you can also put 100 in a cell, copy that cell, select the range concerned and do Edit / Paste Special / Multiply (Assumes data is numeric).

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------