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
12
Medium Priority
?
621 Views
Last Modified: 2012-05-11
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
Comment
Question by:Alan
  • 6
  • 6
12 Comments
 
LVL 30

Expert Comment

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

Sid
0
 
LVL 21

Author Comment

by:Alan
ID: 35688762
Hi Sid,

A UDF would be fine.

Thanks,

Alan.
0
 
LVL 30

Accepted Solution

by:
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 ;-)

Please check the sample attached.

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

Open in new window

Sample.xls
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 30

Expert Comment

by:SiddharthRout
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

by:Alan
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

by:SiddharthRout
ID: 35688890
Glad to be of help :)

Sid
0
 
LVL 21

Author Comment

by:Alan
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

by:SiddharthRout
ID: 35688900
Check now :)

Sid
0
 
LVL 21

Author Comment

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

At least it is right now!

Alan.
0
 
LVL 21

Author Comment

by:Alan
ID: 35688907
So what changed??
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35688911
I also clicked all your comments as helpful :)

Sid
0
 
LVL 21

Author Comment

by:Alan
ID: 35688919
Ahhh.....

Sad that I never knew in all this time.

:-)

Alan.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question