PDA

View Full Version : Solved: Remove certain charachters from a cell



Sarfaraz
03-07-2013, 11:40 PM
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
03-08-2013, 12:57 AM
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

snb
03-08-2013, 02:10 AM
sub M_snb()
cells.replace "PK_",""
cells.replace "CV",""
cells.replace "C",""
end sub

Sarfaraz
03-08-2013, 03:44 AM
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
03-08-2013, 03:46 AM
sorry for inconvenience

patel
03-08-2013, 04:07 AM
my code works on attached workbook, if you want use it on another you have to arrange it or attach the real workbook

mancubus
03-08-2013, 06:32 AM
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
03-08-2013, 09:14 PM
In that case you might need to use something like this.
Assuming the strings are in A2 and down.


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

Sarfaraz
03-09-2013, 03:05 AM
jolivanes, thanks it worked perfectly alright