Converting decimal values ​​to zeros

[shimaa01234]

I want to convert these numbers as in the example:

From	To
10.16	10.00
15.09	15.00
19.99	19.00

<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>
</tbody>

 

[pjmorris]

shimaa,

The easy method would be to insert a dummy column to the right of the column you wish to change, use Special-K99's formula in the new column. Then copy and paste values into the original cells and format as required. Alternatively try running this:

Sub stuff()
    Dim rng As Range
    Dim cell As Range
    
    Set rng = Selection
    For Each cell In rng
        cell = Int(cell)
        cell.NumberFormat = "0.00"
    Next
       
End Sub

Simply highlight the cells you want to convert then run the macro.

Regards

 

[shimaa01234]

Wonderful
Thank you very much Mr. "Pjmorris"
You're a nice guy

 

[pjmorris]

My pleasure and thanks for the feedback.

 

[Peter_SSs]

Try this in a copy of your workbook.

Sub Convert_Data()
  Dim adr As String
  
  adr = "A2:A" & Range("A" & Rows.Count).End(xlUp).Row '<- Change column & start row to suit
  With Range(adr)
    .Value = Evaluate("IF(LEN(" & adr & "),TRUNC(" & adr & "),"""")")
    .NumberFormat = "0.00"
  End With
End Sub

 

[Peter_SSs]

You might also like to try a manual approach?

1. Select the column by clicking its heading label.

2. Ctrl+H to bring up the Replace dialog.

3. Find what: .* -> Replace with: Leave blank -> Options -> Ensure 'Match entire cell contents' is not checked -> Replace All -> OK -> Close

4. Format the column to show 2 decimal places.

 

[shimaa01234]

You always wonderful Mister "Peter"
This is an honor for me
Thank you very much Sir

 

[pjmorris]

Hi Peter,

Always interested to learn of new techniques, but not sure I understand the benefit of the code above. Additionally in other posts I've seen the rng.specialcells(xlcelltypeconstants) method use, but couldn't get it to work with the evaluate method you've used - any suggestions or ideas would be gratefully received:

Sub Convert_Data()
  Dim adr As String
  Dim rng As Range
  
  Set rng = Range(A4", Range("A4").End(xlDown))
  
  With rng.SpecialCells(xlCellTypeConstants)
    .Value = Evaluate("Int(.Value)")
    .NumberFormat = "0.000"
End With

Regards

Peter

 

[Peter_SSs]

Hi Peter,

Always interested to learn of new techniques, but not sure I understand the benefit of the code above. Additionally in other posts I've seen the rng.specialcells(xlcelltypeconstants) method use, but couldn't get it to work with the evaluate method you've used - any suggestions or ideas would be gratefully received:

Sub Convert_Data()
  Dim adr As String
  Dim rng As Range
  
  Set rng = Range(A4", Range("A4").End(xlDown))
  
  With rng.SpecialCells(xlCellTypeConstants)
    .Value = Evaluate("Int(.Value)")
    .NumberFormat = "0.000"
End With

Regards

Peter

Peter

To be honest, I hadn't seen your post #11 when I was composing & posting mine. However, I'd say a benefit is that my code processes the whole range at once, rather than looping through a cell at a time.

In relation to your attempted 'SpecialCells/Evaluate' code, all I'd say is that, for me at least, 'Evaluate' is a tricky beast to get to know. I'd suggest that you search the forum for some more threads that have used 'Evaluate' in suggested code. In recent times I've seen Rick Rothstein use it quite a bit to good effect so you might try to find some of his threads incorporating this.

 

[Peter_SSs]

You always wonderful Mister "Peter"
This is an honor for me
Thank you very much Sir

(Especially if you are referring to me 'Peter' rather than the other Peter 'Peter' ) Glad to help. Did you like the code or the manual method?

 

[pjmorris]

Hi Peter,

It was in one of Rick's posts that I saw the specialcells method hence my attempted approach. I shall dig some more as i think the both your defining adr and the specialcells methods simply identify the relevant range, hence the evaluate ought to work in my solution also - though I note you've defined adr as a string. I'm off to investigate Evaluate further!! If I think I've found anything useful I'll post again

Regards