• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

Contains formula

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
Seamus2626
Asked:
Seamus2626
  • 2
  • 2
2 Solutions
 
Rory ArchibaldCommented:
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
 
Arno KosterCommented:
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
 
Seamus2626Author Commented:
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
 
Rory ArchibaldCommented:
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
 
Seamus2626Author Commented:
I might have to look into getting one of them sorted.

Thanks guys
Seamus
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now