# Removing the Last Two...

#### tourless

##### Board Regular
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``````

### 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
VBA Code:
``````Sub tourless2()
With Range("I2", Range("I" & Rows.Count).End(xlUp))
End With
End Sub``````

#### VBE313

##### Well-known Member
why not use Round and just go two decimal places?

#### tourless

##### Board Regular
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
No good. Rounding skews my totals. I'm trying to work with TRUNC. Validating now.

#### jim may

##### Well-known Member
VBA Code:
``````Sub tourless2()
With Range("I2", Range("I" & Rows.Count).End(xlUp))
End With
End Sub``````
How is the "@" used or interpreted in the below partial code line.

#### Fluff

##### MrExcel MVP, Moderator
The replace function replaces the @ in the formula with .Address

#### Peter_SSs

##### MrExcel MVP, Moderator
Does this variation do what you want?

VBA Code:
``````Sub TwoDP()
With Range("I2", Range("I" & Rows.Count).End(xlUp))
End With
End Sub``````

#### tourless

##### Board Regular
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
As this is a totally different question, it needs a new thread.
Thanks