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

MatthewLane412

New Member
Joined
Aug 23, 2017
Messages
24
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
The first thing to do is change the name of your macro, Never used VBA keywords for the names of Subs or variables.
 
Upvote 0
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:

Code:
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
 
Upvote 0
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:

Code:
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
 
Upvote 0
Yes, not all your cells in that range contain that text, try this:

Code:
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
 
Upvote 0
Scott, this code worked perfect. Thank you again

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

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top