Removing the Last Two...

tourless

Board Regular
Joined
Feb 8, 2007
Messages
68
Office Version
365
Platform
Windows
Hi Folks,

I know this has been asked a thousand times and I'm trying to implement some of the solutions I've found but I just can't seem to get this to work. I have values in column I that four decimal places deep and I simply want to remove the last two characters. The problem I'm noticing is some of the values only contain two digits even though the cells are formatted to four decimal places. So a value of 56.34 is formatted as 56.3400. Other cells have actual values that are three of four decimal places deep, such as a value of 5.7324 is formatted as 5.7324, and a value of 4.242 is formatted as 4.2420. So I guess I need to evaluate each cell in my range and add a few if's to account for the variations? This is what I'm working with.

VBA Code:
For i = 2 To LastRow
    .Cells(i, "I") = Left(.Cells(i, "I").Value, Len(.Cells(i, "I").Value) - 2)
Next i
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,088
Office Version
365
Platform
Windows
How about
VBA Code:
Sub tourless2()
   With Range("I2", Range("I" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@="""","""",round(@,2))", "@", .Address))
   End With
End Sub
 

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
508
Office Version
365
Platform
Windows
why not use Round and just go two decimal places?
 

tourless

Board Regular
Joined
Feb 8, 2007
Messages
68
Office Version
365
Platform
Windows
I was trying to avoid rounding but I can't really think of a good reason why I can't so I'm going to give this a try and test out some totals. If I total each section before and after the rounding I might have a variance which I would love to add to the last line of a given section but let's see how this works out. I'll keep you posted...
 

tourless

Board Regular
Joined
Feb 8, 2007
Messages
68
Office Version
365
Platform
Windows
No good. Rounding skews my totals. I'm trying to work with TRUNC. Validating now.
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,471
How about
VBA Code:
Sub tourless2()
   With Range("I2", Range("I" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@="""","""",round(@,2))", "@", .Address))
   End With
End Sub
How is the "@" used or interpreted in the below partial code line.

Replace("if(@="""","""",round(@,2))", "@", .Address)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,088
Office Version
365
Platform
Windows
The replace function replaces the @ in the formula with .Address
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,734
Office Version
365
Platform
Windows
Does this variation do what you want?

VBA Code:
Sub TwoDP()
  With Range("I2", Range("I" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(#="""","""",int(#*100)/100)", "#", .Address))
  End With
End Sub
 

tourless

Board Regular
Joined
Feb 8, 2007
Messages
68
Office Version
365
Platform
Windows
Thanks for all the replies everyone... I'm using this for now although it's not perfect it leaves me a lot closer to my goal and eliminates a lot of time manually adjusting dollar values.
VBA Code:
    Range("J2:J" & lastRow).Formula = "=TRUNC(I2,2)"
    Range("J2:J" & lastRow).Copy
    Range("I2").PasteSpecial xlPasteValues
    Range("J2:J" & lastRow).ClearContents
Thanks all.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,088
Office Version
365
Platform
Windows
As this is a totally different question, it needs a new thread.
Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,099,802
Messages
5,470,864
Members
406,732
Latest member
Micka77

This Week's Hot Topics

Top