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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,116
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

MatthewLane412

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

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

MatthewLane412

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

Watch MrExcel Video

Forum statistics

Threads
1,108,684
Messages
5,524,272
Members
409,567
Latest member
Shadh

This Week's Hot Topics

Top