Solved

Contains formula

Posted on 2011-02-14
5
207 Views
Last Modified: 2012-05-11
Hi,

In a cell i could have different words

I want an if statement to say

If cell AF2 contains these three words
"Awaiting Booking Amendment"

Return the text

Awaiting Booking Amendment

This will be a multiple ie as i have three or four different combos of words im searching for.

I.E

Cleared Today
Open Investigation

Thanks
Seamus
0
Comment
Question by:Seamus2626
  • 2
  • 2
5 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 300 total points
ID: 34887287
A few questions:
1. Will that be the only text in the cell?
2. Can there be multiple matches of the search text? (e.g. could a cell contain both your search values)
3. If the answer to 2 is yes, what do you want returned?
4. Are the search values stored in a cells somewhere?
0
 
LVL 19

Assisted Solution

by:akoster
akoster earned 200 total points
ID: 34887288
In general, the corresponding VBA if statement would be :

This function should be placed in a module.
Public Function statement() As String

    '-- initialise
    statement = ""
    
    '-- condition
    If ActiveSheet.Range("AF2") = "Awaiting Booking Amendment" Then
        statement = "Awaiting Booking Amendment"
    End If

End Function

Open in new window



0
 

Author Comment

by:Seamus2626
ID: 34887300
Hey Rory,

There could be other text in the cell and the text "Awaiting Booking Amendment" could be mispelt.

There wont be multiple matches within the cell

There currently stored elsewhere but i could definately make a list some where e.g. BA2 to BA12

Thanks
Seamus
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34887326
If it could be misspelled, then you are out of luck. Best you could get would be some sort of fuzzy matching function (and I don't have time to write one of those). The rest is fairly simple:

=LOOKUP(2,1/(ISNUMBER(SEARCH($BA$2:$BA$12,A1))),$BA$2:$BA$12)

0
 

Author Closing Comment

by:Seamus2626
ID: 34887454
I might have to look into getting one of them sorted.

Thanks guys
Seamus
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now