Truncate Characters from a Cell

[noboffinme]

Hi

I have the below code to remove 4 characters from the cells contents

I find it isn't having any effect on the cells though??

Can anyone explain where this code is wrong, I want to adapt it to be used to truncate from the start or the end of the cells contents & be used with Text & also Numbers.

---------------------------------

Sub right_char_trim()
Dim i As Integer


For i = Application.ActiveSheet.Range("A65536").End(xlUp).Row To 2 Step -1


ActiveCell.Activate


Cells(i, 1) = Right(Cells(i, 1), Len(Cells(i, 1) - 4))


Next i

End Sub

---------------------------------------------

 

[HaHoBe]

Hi, noboffinme,

wrong position of parenthesis for the length of the Cells-Value?

Sub right_char_trim()
Dim i As Long

For i = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
    Cells(i, 1) = Mid(Cells(i, 1), 1, Len(Cells(i, 1)) - 4)
Next i

End Sub

To indicate whether the start or the end of the cells contents should be viewed I´d use a parameter to pass to decide which code should be used:

Sub right_char_trim(blnStart As Boolean)
Dim i As Long

For i = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
    If blnStart Then
        Cells(i, 1) = Left(Cells(i, 1), Len(Cells(i, 1)) - 4)
    Else
        Cells(i, 1) = Mid(Cells(i, 1), 1, Len(Cells(i, 1)) - 4)
    End If
Next i

End Sub

Sub Call_Proc()
right_char_trim False
End Sub

The Range as well as the Sheet could be passed here as well.

Ciao,
Holger

 

[noboffinme]

Thanks, perfect !!