Solved: delete part of string in every string

[benong]

hi,
i have rows & rows of string in column A.
..2 0021 313922364983 PTR KWB900001 MAIN 1 PCE X 18.05.2010 0
....
....
Every rows contains different set of data, but this word "KWB900001" appears on every row.

So now, on every row, I want to remove all the characters at the back of the string, starting with this word: KWB900001

eg. ..2 0021 313922364983 PTR KWB900001 MAIN 1 PCE X 18.05.2010 0
become -> ..2 0021 313922364983 PTR

Can someone guide me on this, thanks.

[GTO]

Try:


Option Explicit

Sub RemoveKWB()
Dim wksData As Worksheet
Dim rngData As Range
Dim rngCell As Range
'// Change sheetname to suit //
Set wksData = ThisWorkbook.Worksheets("MySheet")
'// Presumes a header row //
With wksData
Set rngData = .Range("A2:A" & _
Application.Max(2, .Cells(.Rows.Count, 1).End(xlUp).Row))
End With

For Each rngCell In rngData
If InStr(1, rngCell.Value, "KWB900001") > 0 Then

rngCell.Value = _
Trim(Left(rngCell.Value, InStr(1, rngCell.Value, "KWB900001") - 1))
End If
Next
End Sub

[benong]

Dear GTO, many thanks for your fast response, it works great :)

[GTO]

You are most welcome :-)