If certain text appears in a cell

fahimip

New Member
Joined
Sep 27, 2006
Messages
35
I need to write an if function code so that if a certain sequence of letters appears in a cell then it carries out a task.
the only problem is that the sequence of text will be surrounded by other letters or numbers.


Any ideas Guys?

any help will be massively appreciated!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
On a worksheet you can use eg:

=IF(ISNUMBER(SEARCH("abc",A1)),"Found","Not found")

In VBA you can use:

Code:
If Instr(4, Range("A1"), "abc") > 0 Then
   MsgBox "Found"
Else
   MsgBox "Not found"
End If
 

fahimip

New Member
Joined
Sep 27, 2006
Messages
35
Andrew thanks for your help, but could you please explain the VB code to me?
im pretty new at this.

And also will excel be able to find letters that are hidden between other letters in a cell, using this code?
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Andrew

I'm intrigued - why do you start the search at character position 4 in the above VBA example?

Richard
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Hi Andrew

I'm intrigued - why do you start the search at character position 4 in the above VBA example?

Richard

Because I have fat fingers. :eek: It should of course be 1.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Andrew thanks for your help, but could you please explain the VB code to me?
im pretty new at this.

And also will excel be able to find letters that are hidden between other letters in a cell, using this code?

Yes to your second question.

What sort of explanation of the VB code do you want? It looks self explanatory to me.
 

fahimip

New Member
Joined
Sep 27, 2006
Messages
35

ADVERTISEMENT

it doesnt seem to be working for some reason, heres what ive got;

range("b2").Select
If InStr(7, range("b2"), "sw") > 0 Then
Rows("1:1").Select
Selection.AutoFilter Field:=10, Criteria1:="=*shaft*", Operator:=xlAnd

[/quote]
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
This worked for me:

Code:
Sub Test()
    If InStr(1, Range("B2").Value, "sw") > 0 Then
        Rows("1:1").AutoFilter Field:=10, Criteria1:="=*shaft*", Operator:=xlAnd
    End If
End Sub

But why are you filtering only row 1?
 

fahimip

New Member
Joined
Sep 27, 2006
Messages
35
thanks andrew, i didnt realise it was case sensitive.
it works now.


thanks for your help
 

Forum statistics

Threads
1,136,878
Messages
5,678,326
Members
419,756
Latest member
vincent86kapelski

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
Top