Excel VBA InStr Function to delete string of text within a cell

[MatthewLane412]

I have a strings of text similar to the following (4010059877-TUR36036133 or TUR603916## 401007329) in column "AK".I need to Eliminate the TUR######## which can be at any point in the cell and has a different number behind it each time. Is There a way to delete only this part of the cell?

I have been working with InStr function but cant get it to work. I have the idea behind it.

Loc = instr(mycell,"TUR")
val = left(mycell.value,loc-1)


Here's my attempt which I get error code "Invalid procedure call or argument"

Sub Instr ()
Dim Loc as Long
Dim Val as Integer

Dim finRow As String
finRow=Sheets("Data").Range("A20000").End(xlUp).Row
Set myRange = Sheets("Data").Range ("AK2:AK" & finRow)
For Each myCell in myRange
Loc=InStr(myCell.Value, "TUR")
Val = Left (myCell.Value, Loc - 1)

End Sub

Thanks for looking, Matt

 

[Fluff]

The first thing to do is change the name of your macro, Never used VBA keywords for the names of Subs or variables.

 

[Scott Huish]

I'm assuming you're just trying to return the number other than the part that starts with TUR. Is your number after TUR always 8 digits, if so:

Sub TUR()
Dim c As Range
For Each c In Worksheets("Data").Range("AK2:AK" & Range("AK" & Rows.Count).End(xlUp).Row)
    c = Replace(c, "-", "")
    c = Application.WorksheetFunction.Replace(c, InStr(1, c, "TUR"), 11, "")
Next
End Sub

 

[MatthewLane412]

You are correct. The number after TUR is always 8 digits. When I run the code I get the following error. "Run-time error '1004': Unable to get the Replace property of the worksheet function class. Any ideas? Thank you

I'm assuming you're just trying to return the number other than the part that starts with TUR. Is your number after TUR always 8 digits, if so:

Sub TUR()
Dim c As Range
For Each c In Worksheets("Data").Range("AK2:AK" & Range("AK" & Rows.Count).End(xlUp).Row)
    c = Replace(c, "-", "")
    c = Application.WorksheetFunction.Replace(c, InStr(1, c, "TUR"), 11, "")
Next
End Sub

 

[Scott Huish]

Yes, not all your cells in that range contain that text, try this:

Sub TUR()
Dim c As Range
For Each c In Worksheets("Data").Range("AK2:AK" & Range("AK" & Rows.Count).End(xlUp).Row)
    c = Replace(c, "-", "")
    If InStr(1, c, "TUR") Then c = Application.WorksheetFunction.Replace(c, InStr(1, c, "TUR"), 11, "")
Next
End Sub

 

[MatthewLane412]

Scott, this code worked perfect. Thank you again

Yes, not all your cells in that range contain that text, try this:

Sub TUR()
Dim c As Range
For Each c In Worksheets("Data").Range("AK2:AK" & Range("AK" & Rows.Count).End(xlUp).Row)
    c = Replace(c, "-", "")
    If InStr(1, c, "TUR") Then c = Application.WorksheetFunction.Replace(c, InStr(1, c, "TUR"), 11, "")
Next
End Sub