Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Contains formula

Posted on 2011-02-14
5
Medium Priority
?
242 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 1200 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:Arno Koster
Arno Koster earned 800 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

824 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