Divide entire column by 100

  • Thread starter Thread starter SITCFanTN
  • Start date Start date
S

SITCFanTN

I have a spreadsheet where the currency value are formatted incorrectly.
Values of
227.00 are shown as $22,700.00, 136.00 is shown as $13,600.00 aqnd 1331.00
is shown as 133,100.00. I need all the values in column I to be divided by
100 with the $ sign omitted. Can anybody help me with the VB code for this.
Thanks so much
 
put 100 in a cell

copy it

select column A, then paste special
click divide under operation

then just format the column without the $ with 2 decimal.
 
Before you go dividing by 100, let's make sure we know what is going on. If
you select the cell that says $22,700.00 (but is supposed to be 227), what
does it say in the Formula Bar? Also, right-click the cell, select the
Format Cells item from the popup menu that appears and click the Number tab
on the dialog box that appears. What item in the Category listing is
selected? If there are any listings or fields shown under the Sample field,
what values are in them?

Rick
 
Thanks Rick for your help...here are the answers to your questions

Question 1 = 22700
Question 2= Currency
Data in Sample field = $22,700.00

I need the pennies to show so what I need the field to look like is 227.00.

I hope this helps, thanks so much.
 
Okay, that answer raises some more questions and/or possible solutions. Note
the emphasis in the following... IF you will NOT be using these values in
any further/future calculations, then you can make the value "look" like
what you want (but note that it will NOT actually be that value; hence the
caution about future use) by simply formatting the entry. Just use the same
procedure I asked you to follow in my previous response and select Custom
from the Category list and put 0\.00 in the Type field.

However, I'm willing to be you will want to use this value in other
calculations. If that is the case, my first question is why not use standard
entries? For 227, just type in 227 and, also, remove the format from the
cells in that column (change the Currency selection to General in the
Formatting Cells dialog). For values with penny amounts, just type in the
decimal point where it would normally be. Do you really think the saving
from typing this on additional character is worth the trouble it causes?

If you are insistent on not typing in the decimal point, then my next
question is... will you be creating all your entries in this column only one
time? If so, follow the procedure Gary laid out for you. If you will be
making multiple entries in this column over time, then you will need an
event procedure to handle adjusting the value. To do this, right click the
tab for the worksheet with this column on it and select View Code from the
menu that pops up. Copy/Past the following into the code window that
appears...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
On Error GoTo Whoops
If InStr(Target.Value, ".") = 0 Then
Application.EnableEvents = False
Target.Value = Target.Value / 100
Application.EnableEvents = True
On Error GoTo 0
End If
End If
Exit Sub
Whoops:
Application.EnableEvents = True
End Sub

Oh, and make sure to remove the format from the cells in that column (change
the Currency selection to General in the Formatting Cells dialog).

Rick
 
Okay, that answer raises some more questions and/or possible solutions. Note
the emphasis in the following... IF you will NOT be using these values in
any further/future calculations, then you can make the value "look" like
what you want (but note that it will NOT actually be that value; hence the
caution about future use) by simply formatting the entry. Just use the same
procedure I asked you to follow in my previous response and select Custom
from the Category list and put 0\.00 in the Type field.

However, I'm willing to be you will want to use this value in other
calculations. If that is the case, my first question is why not use standard
entries? For 227, just type in 227 and, also, remove the format from the
cells in that column (change the Currency selection to General in the
Formatting Cells dialog). For values with penny amounts, just type in the
decimal point where it would normally be. Do you really think the saving
from typing this on additional character is worth the trouble it causes?

If you are insistent on not typing in the decimal point, then my next
question is... will you be creating all your entries in this column only one
time? If so, follow the procedure Gary laid out for you. If you will be
making multiple entries in this column over time, then you will need an
event procedure to handle adjusting the value. To do this, right click the
tab for the worksheet with this column on it and select View Code from the
menu that pops up. Copy/Past the following into the code window that
appears...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 4 Then
    On Error GoTo Whoops
    If InStr(Target.Value, ".") = 0 Then
      Application.EnableEvents = False
      Target.Value = Target.Value / 100
      Application.EnableEvents = True
      On Error GoTo 0
    End If
  End If
  Exit Sub
Whoops:
  Application.EnableEvents = True
End Sub

Oh, and make sure to remove the format from the cells in that column (change
the Currency selection to General in the Formatting Cells dialog).

Rick










- Show quoted text -
Click to expand...

Hi,

One thing I am not clear on is whether you want new data entered to be
divided by 100 (which appears to be Rick's interpretation from the
Worksheet_Change suggestion he offered, or if you have been given a
stack of data that is already in this format in which case Gary's
PasteSpecial method would be the simplest.
If you are keying this new data, one other suggestion would be to head
to 'Tools' -> 'Options' -> 'Edit' tab, and tick 'Fixed decimal places'
with a setting of -2. This will mean that every value you enter will
be automatically divided by 100 without having to run a Change macro.
If you already have the source data and it is not still being added
to, the following macro will do what you asked, but it does involve a
loop which Gary's method avoids:

Sub DivideCells()
Dim cell As Range

For Each cell In Range("I:I").SpecialCells(xlCellTypeConstants,
xlNumbers)
cell = cell.Value / 100
Next
Range("I:I").NumberFormat = "General"
End Sub

Cheers,
Ivan.
 
I have a spreadsheet where the currency value are formatted incorrectly. Values of 227.00 are shown as $22,700.00, 136.00 is shown as $13,600.00 aqnd 1331.00 is shown as 133,100.00. I need all the values in column I to be divided by 100 with the $ sign omitted. Can anybody help me with the VB codefor this. Thanks so much
Click to expand...

Thanks a lot for your help..
Really appreciate it
 
Thanks a lot for your help..
Really appreciate it
Click to expand...

export as text, set number format to use , and . as separators, import
back to excel. It should recognize them as numbers. divide by 100.
change number format back to original one.
 
hi,

write the number 100 in a cell, make a copy of this cell, select the range and do a paste special - division

--
isabelle



Le 2012-08-30 16:42, (e-mail address removed) a écrit :
 
Back
Top