[if statement] search for string of text then spit out a result. Help!

[desiprince1212]

Hi everyone!

I have a quick question --

I wanted to learn to write and IF statement that would search for certain words. If it's not there then say this, if it does have one of the said words then write this. How would I do that?

Here is an example of what I need to do:

Column, Row
A, 1
The John Doe Charitable TR Trust Lisa Doe and Romano Trustee

In the next column, I want the forumula to search for the following words, "Irr", "Irrev", "Charitable" -- if he has the words (like it does above) then I want it to say "Not covered". Then I want another one to say if it has the word "corp", "corporation" then I want it to say corporation. If it does not have any of those parameters leave it blank.

Is there an efficient way to do this please? Thanks!!

 

[JLGWhiz]

Sub findWords()
Dim c As Range
    For Each c In Range("B2", Cells(Rows.Count, 2).End(xlUp))
        If InStr(c, "Irr") > 0 Or InStr(c, "Irrev") > 0 Or InStr(c, "Charitable") > 0 Then
            c.Offset(0, 1) = "Not Covered"
        ElseIf InStr(LCase(c), "corp") > 0 Or InStr(LCase(c), "corporation") > 0 Then
            c.Offset(0, 1) = "Corporation"
        End If
    Next
End Sub

 

[Mackers]

Hi everyone!

I have a quick question --

I wanted to learn to write and IF statement that would search for certain words. If it's not there then say this, if it does have one of the said words then write this. How would I do that?

Here is an example of what I need to do:

Column, Row
A, 1
The John Doe Charitable TR Trust Lisa Doe and Romano Trustee

In the next column, I want the forumula to search for the following words, "Irr", "Irrev", "Charitable" -- if he has the words (like it does above) then I want it to say "Not covered". Then I want another one to say if it has the word "corp", "corporation" then I want it to say corporation. If it does not have any of those parameters leave it blank.

Is there an efficient way to do this please? Thanks!!

Try this out:

=IF(SUM(COUNTIF(A1, {"*Irr *", "* Irr*", "*Irrev *", "* Irrev*", "*Charitable *", "* Charitable*"}))>0, "Not coverred", IF(SUM(COUNTIF(A2, {"* corp*", "corp *"}))>0, "Corporation", ""))

Hope that helps

Mackers

Edit: regarding the above post, it seems very efficient but I would first check if it returns a false positive for the word "scorpion", for example, as it contains the word "corp". My formula includes a requirement that the word be followed by or preceded by a space.

 

[desiprince1212]

Holy smokes! You guys are amazing. Thank you so much! If I am starting to learn now, any ideas where to learn or look?

 

[JLGWhiz]

Try this out:

=IF(SUM(COUNTIF(A1, {"*Irr *", "* Irr*", "*Irrev *", "* Irrev*", "*Charitable *", "* Charitable*"}))>0, "Not coverred", IF(SUM(COUNTIF(A2, {"* corp*", "corp *"}))>0, "Corporation", ""))

Hope that helps

Mackers

Edit: regarding the above post, it seems very efficient but I would first check if it returns a false positive for the word "scorpion", for example, as it contains the word "corp". My formula includes a requirement that the word be followed by or preceded by a space.

You're right Mackers, and that is the falacy of responding to a post that is not clear and concise about what the worksheet which is to be dealt with contains. But we can work that out as we go along.

 

[desiprince1212]

Try this out:

=IF(SUM(COUNTIF(A1, {"*Irr *", "* Irr*", "*Irrev *", "* Irrev*", "*Charitable *", "* Charitable*"}))>0, "Not coverred", IF(SUM(COUNTIF(A2, {"* corp*", "corp *"}))>0, "Corporation", ""))

Hope that helps

Mackers

Edit: regarding the above post, it seems very efficient but I would first check if it returns a false positive for the word "scorpion", for example, as it contains the word "corp". My formula includes a requirement that the word be followed by or preceded by a space.

Wow, this is great as well. so i can paste this in one cell then drag it down to 500. and if it's "not covered" or "corporations" then it will leave it blank? Also, why did you use "{" what is that for?

Where did you learn all of these neat things? I'd love to learn more. Thanks!

 

[JLGWhiz]

wow, this is great as well. so i can paste this in one cell then drag it down to 500. and if it's "not covered" or "corporations" then it will leave it blank?

It will not leave it blank, the formula will be there, but it will appear to be blank with a value of "".

 

[Mackers]

Wow, this is great as well. so i can paste this in one cell then drag it down to 500. and if it's "not covered" or "corporations" then it will leave it blank? Also, why did you use "{" what is that for?

Where did you learn all of these neat things? I'd love to learn more. Thanks!

Let's say if you had the numbers 3, 5 and 7 in cells A1, A2 and A3. If you had a formula such as COUNTIF(A1:A3, ">4"), when Excel actually executes the function it returns the range A1:A3 as an array in curly braces, such as =countif({3; 5; 7}, ">4"). It then counts how many of the cells are greater than 4, returning 2. You can see this by highlighting the A1:A3 in your formula bar and pressing F9, or using the formula evaluation tool. What I have done is specified multiple criteria by manually typing an array using curly braces, and then used SUM() to aggregate the criteria, i.e. the formula is performing all of the counts, then adding them up.

You can do the equivalent by typing =countif(range, criteria1)+countif(range,criteria2)+countif(range,criteria3)+countif(range,criteria4)
But it is faster and more readable to write =sum(countif(range,{criteria1,criteria2,criteria3,criteria4})).

Regarding the logic of the formula, what it does in plain English is:

1. Count the total number of words contains the string Irr, Irrev or Charitable at the beginning or end of the word. This will return a false positive on words like Irritation, so the formula could definitely be improved.
2. If this total calculated in 1. is greater than 0, it writes "Not coverred" (my typo, oops).
3. If not, it calculates the total number of words containing the string Corp at the beginning or end of the word. I didn't bother adding Corporation as it is encompassed by Corp (similarly I could have removed Irrev from 1.).
4. If this total calculated in 3. is greater than 0, it writes "Corporation"
5. If none of the above criteria is met, it returns a blank string "" (you can see this at the end of the formula)

Regarding where we learn these things, foir me it was through trial and error, reading the web (including this forum), taking courses, youtube tutorials and some understanding of maths and what you think you should be able to achieve.

Hope that helps

Mackers