Delete characters if matching

[koala]

:bow:

I need some help to get a code to do the following.

1) check each cell in range a1:a336 (all cells contain text of varying length)
2) if the text ends in "z", delete the last 3 characters of the cell, if it ends in anything else leave it as it is.

I need to do this daily due to how i recieve an imported file and it takes me ages to do it manually.

Hopeing someone can assist : pray2:

Koala

[Bob Phillips]

Dim cell As Range
For Each cell In Range("A1:A336")
If LCase(Right(Trim(cell.Value), 1)) = "z" Then
cell.Value = Left(cell.Value, Len(cell.Value) - 3)
End If
Next cell

[koala]

Thanks xld,

This looks like what I need

I will try this tomorrow and let you know if it is.

Koala

[koala]

Thanks xld,

This works a treat and is exactly what I needed.

Thank you for the quick reply, you have saved me many hours of painstaking, boring, repetitive work

cheers
Koala

[brettdj]

You might want to consider a Find or variant array approach, as For ... Next range loops can get expensive time wise, especially when they write a result cell by cell

The code below does the same job with an additional check that the string is at least four characters long before the deletion.


Sub KillZ()
Dim X As Variant, i As Long
X = Range("A1:A336")
For i = 1 To UBound(X, 1)
If Right(Trim(X(i, 1)), 1) = "z" And Len(Trim(X(i, 1))) > 3 Then X(i, 1) = Left(X(i, 1), Len(X(i, 1)) - 3)
Next
Range("A1:A336") = X
End Sub


if your data was variable in length and you wanted to work from A1 to the last used cell in column A the the code below is flexible. Although if you didn't have many "z"s, then a Find routine would be more efficient again.



Sub KillZ()
Dim X As Variant, i As Long
X = Range([a1], [a65536].End(xlUp))
For i = 1 To UBound(X, 1)
If Right(Trim(X(i, 1)), 1) = "z" And Len(Trim(X(i, 1))) > 3 Then X(i, 1) = Left(X(i, 1), Len(X(i, 1)) - 3)
Next
Range([a1], [a65536].End(xlUp)) = X
End Sub

Cheers

Dave