Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Excel - Formula to find text string within a range

Posted on 2011-05-04
Medium Priority
621 Views
Hi All,

Not sure if this is trivial and I am just having a mental block, but I need a formula that will identify if a text string exists anywhere within a range, including within a formula in a cell in that range.

Something akin to this (I know that FINDSTRING is not a standard function in excel - this is just to illustrate):

=FINDSTRING("Alan",A1:B1)

If A1 and B1 contain the following:

A1 = "Bananas"
B1 = If(A1="Apples","Alan","Bob")
= "Bob"

then I need the formula to return TRUE (or whatever) to identify that the string "Alan" exists in the range even though, at this point in time, neither A1 nor B1 evaluates to "Alan".

Hope that makes sense, but if not, please post back for clarification.

Thanks,

Alan.

0
Question by:Alan
• 6
• 6

LVL 30

Expert Comment

ID: 35688728
Would you like a vba code (User Define Function?

Sid
0

LVL 21

Author Comment

ID: 35688762
Hi Sid,

A UDF would be fine.

Thanks,

Alan.
0

LVL 30

Accepted Solution

SiddharthRout earned 2000 total points
ID: 35688775
Ok :)

>>>(I know that FINDSTRING is not a standard function in excel - this is just to illustrate)

Well Guess What? Now you have a UDF with that Name ;-)

Hope this is what you wanted?

Sid

Code Used

``````Public Function FINDSTRING(strText As String, Rng As Range) As Boolean
Dim aCell As Range, bCell As Range

On Error GoTo Whoa

Set aCell = Rng.Find(What:=strText, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not aCell Is Nothing Then
FINDSTRING = True
Exit Function
Else
Set bCell = Rng.Find(What:=strText, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not bCell Is Nothing Then
FINDSTRING = True
Exit Function
End If
End If
Whoa:
End Function
``````
Sample.xls
0

LVL 30

Expert Comment

ID: 35688828
BTW, you may delete the lines 12 and 20. I had kept it for testing and forgot to remove it.

Sid
0

LVL 21

Author Closing Comment

ID: 35688887
Fantastic.

I think I could have gotten there myself if I had thought it through properly, but sometimes you get a mental block and just can't see a way forward.

Very much appreciated.

Alan.
0

LVL 30

Expert Comment

ID: 35688890
Glad to be of help :)

Sid
0

LVL 21

Author Comment

ID: 35688893
BTW, any idea how the site gets the '9.1' rating?

I ticked every box as 'perfect', but it still doesn't give a '10'?

Not a biggie, but just interested.

Alan.
0

LVL 30

Expert Comment

ID: 35688900
Check now :)

Sid
0

LVL 21

Author Comment

ID: 35688902
Okay - that's weird.  Now its a 'perfect ten'.

At least it is right now!

Alan.
0

LVL 21

Author Comment

ID: 35688907
So what changed??
0

LVL 30

Expert Comment

ID: 35688911

Sid
0

LVL 21

Author Comment

ID: 35688919
Ahhh.....

Sad that I never knew in all this time.

:-)

Alan.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question