Solved: Remove certain charachters from a cell

[Sarfaraz]

Hi,
I have a worksheet column in which few of the cells have some unwanted characters and I wanted to remove those unwanted characters from these cells like
PK_TTA121 in this remove "PK_" and change this to TTA121
PK_TLU955CV in this remove "PK_" & "CV" and change this to TLU955
PK_LSB6882C in this remove "PK_" & "C" and change this to LSB6882
TTB966 no change
TLS5263C in this remove "C" and change this to TLS5263
Would be grateful if somebody help me in writhing the code to do this automatically. I am also attaching the example sheet
Regards

[patel]

[VBA]Sub aa()
LR = Cells(Rows.Count, "E").End(xlUp).Row
For j = 2 To LR
st = Cells(j, 5)
pos = InStr(st, "_")
If pos > 0 Then
st = Right(st, Len(st) - pos)
End If
For i = Len(st) To 1 Step -1
If Not IsNumeric(Mid(st, i, 1)) Then
st = Left(st, i - 1)
Else
Exit For
End If
Next
Cells(j, 6) = st
Next
End Sub[/VBA]

[snb]

[VBA]
sub M_snb()
cells.replace "PK_",""
cells.replace "CV",""
cells.replace "C",""
end sub
[/VBA]

[Sarfaraz]

Thanks Patel & snb, for your valuable help. Unfortunately the code provided by Patel didn't work in my file. But the code provided by snb worked fine except it removed "C" from the original tank lorry number instead I wanted "C" at the end to remove example
TTC966 no change
TTB966C to be changed to TTB966

[Sarfaraz]

sorry for inconvenience

[patel]

my code works on attached workbook, if you want use it on another you have to arrange it or attach the real workbook

[mancubus]

sarfaraz,

you should provide a "pattern" for this type of string manipulation.

if it is up to me, i can say, you want to extract all the numbers and 3 letters before the first occurence of a number from a string.

is this the case?

or are there any strings such as X12345678, XX98645, etc (X being any letter)?

to be more specific... do the strings always contain first 3 (or more) letters and then numbers?

[jolivanes]

In that case you might need to use something like this.
Assuming the strings are in A2 and down.

[VBA]
Sub Try_This()
Dim c As Range
Application.ScreenUpdating = False
For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
c.Replace "PK_", ""
c.Replace "CV", ""
If Mid((c.Value), Len(c.Value), 1) = "C" Then c.Value = Left(c.Value, Len(c.Value) - 1)
Next c
Application.ScreenUpdating = True
End Sub
[/VBA]

[Sarfaraz]

jolivanes, thanks it worked perfectly alright