VBA: Copy Cell to another and remove last 2 characters

[sean.golds]

Hi,

Could someone please tell me how to copy a text string from a cell in Excel and paste in to another minus the last two characters in VBA?

For example if my original cell was 333.2cm, I would like the pasted cell to be 333.2

Thanks

[mdmackillop]

Assuming your cell is formatted as text, otherwise fix the formatting.

For Each cel In Selection
cel.Offset(, 1) = Left(cel, Len(cel) - 2)
Next

[sean.golds]

mdmackillop,

That's great thanks. I have used:


Sub Button1_Click()


Range("D3,D10,D17,D24,D31,D38").Select
For Each cel In Selection
cel.Offset(, 8) = Left(cel, Len(cel) - 3)
Next


End Sub

There will be up to 8 fields with the data in but not always. How do I stop it throwing up an error when if one of those cells are empty?

Thanks

[mdmackillop]

Avoid selecting, refer to ranges directly

Sub Button1_Click()
For Each cel In Range("D3,D10,D17,D24,D31,D38")
If Len(cel) > 3 Then cel.Offset(, 8) = Left(cel, Len(cel) - 3)
Next
End Sub

Please use code tags when yoiu post code