Solved

Contains formula

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

757 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

21 Experts available now in Live!

Get 1:1 Help Now