Solved

Contains formula

Posted on 2011-02-14
5
213 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
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…

773 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