Help with text data

[gl45]

I would like to loop through text data in column e3:e200
and truncate the small c at the right end of the data.
The data is alphanumeric, and also contains some empty cells.
OC4000c or ABCDE10000CL1$3Xc
the result should read OC4000 OR ABCDE10000CL1$3X
Thanks

 

[VoG]

Try

Sub Noc()
Dim i As Integer
For i = 3 To 200
    With Range("E" & i)
        If .Value <> "" Then .Value = Left(.Value, Len(.Value) - 1)
    End With
Next i
End Sub

 

[gecs]

In F3 =IF(RIGHT(E3,1)="c",LEFT(E3,LEN(E3)-1),IF(LEN(E3)=0,"",E3). Copy down the formula to row 200 on column F, select F3:F200 ->Copy, go to E3, right-click -> Paste Special... -> Values -> OK; delete the formulas in column F.

 

[gl45]

Mr. VO
Thanks for the macro, but there is a glitch.

ABD3500N2Lc the result after the macro is run is correct as ABD3500N2L

the macro also does truncate the last character even if there is no c at the end
I only need the small c to be truncate if does appear in the text data.
Thanks

MR, gegs
your "if" statement does work , but I was looking for a way to avoid the copy and paste process every time I download the data.
Thank you.

 

[VoG]

Try

Sub Noc()
Dim i As Integer
For i = 3 To 200
    With Range("E" & i)
        If .Value <> "" Then
            If Right(.Value, 1) = "c" Then .Value = Left(.Value, Len(.Value) - 1)
        End If
    End With
Next i
End Sub

 

[gecs]

Then adjust VoG's macro with my formula

Sub Noc()
Dim i As Integer
For i = 3 To 200
    With Range("E" & i)
        If .Value <> "" Then 
            If Right(.Value,1) = "c" Then
                .Value = Left(.Value, Len(.Value) - 1)
            End If
       End If
    End With
Next i
End Sub

 

[gl45]

Thank you gents both macro did work fine,