CODEWITHSUNDEEP
vbaexceltruncated
Steven
Steven

Reputation: 119

Truncating data in excel (delete the first 12 characters)

This is what I have so far:

Sub Truncate()
    Application.ScreenUpdating = False

    Dim LastHeader As Long
    Dim MyCell As Range
    LastHeader = Range("IV1").End(xlToLeft).Column

    For Each MyCell In Application.Intersect(Sheet6.UsedRange, Columns("I"))
        MyCell.Value = Right(MyCell.Value, Len(MyCell.Value) - 12)
    Next MyCell

    Application.ScreenUpdating = True

End Sub

I want to have this macro delete the first 12 characters in column "I" while leaving the rest how it was. I got this macro to work for other columns when I didn't use (Len(MyCell.Value) - 12), but rather just input a number like Right(MyCell.Value, 1), which kept the first letter on the right only. But for this column, the amount of digits on the right on the data varies, so it would be best to just delete a specified number of characters on the left of the data.

Oh and btw, the error I get is run-time error '5'

Thanks for any help :)

Upvotes: 2

Views: 585

Answers (1)

barrowc
barrowc

Reputation: 10679

Almost certainly one of the entries has 12 or fewer characters so you've ended up calling Right with either 0 or a negative number as the second parameter.

If you want to leave the cell blank if there are 12 or fewer characters then try:

If (Len(MyCell.Value) > 12) Then
    MyCell.Value = Right(MyCell.Value, Len(MyCell.Value) - 12)
Else
    MyCell.Value = ""
End If

Upvotes: 1

Related Questions