Searching text up to specific point in cell

[McTracy]

I'm trying to search for specific words in a cell and assign a code if any of the words are present. I want to search the cell only up to a certain point. Here's an example of one of the text strings I'm searching:

We Removed $6000 from our retirement account for a home purchase. Can we enter this amount into the state return category where it asks "Benefits not eligible for deduction" ***************************************************************** Source: Answer
Center (OL) FAQs Viewed: 8863 - Education Credits (930) American Opportunity
Credit First Four Years (949) Educator Expense Deduction - Teacher (1126)

I want to search for specific words and return the number 112 if any of those words exist. Right now, I have the formula:

=IF(ISNUMBER(SEARCH("Retire",$N2)),112,IF(ISNUMBER(SEARCH("1099",$N2)),112,IF(ISNUMBER(SEARCH("IRA",$N2)),112,IF(ISNUMBER(SEARCH("distribution",$N2)),112,IF(ISNUMBER(SEARCH("annuity",$N2)),112,IF(ISNUMBER(SEARCH("profit*sharing",$N2)),112,""))))))

It works, but I want to stop searching the text at the words "Source:Answer".

Could someone help me adjust my formula to stop the search mid string (and maybe consolidate what I have now)?

Thanks!

 

[cicconmr]

Why are you using ISNUMBER?

Any way if it is working than maybe I just don't understand what you are doing....you can open the VBE and do this in there:

This is very rough, Obviously you'll have to ad some looping technique if the words could be in multiple cells....

Dim Symbol as String, Dim Range as Found

Symbol = What ever you want it to be

Found = Range("N2").Find(What:=Symbol, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)

If Not Found Is Nothing Then
Do what ever you want to do with the 112
Exit Sub
End If

The way I have it symbol is a single string...I'm not sure if there is a way to make an array of strings someone else might have a faster way of doing that.

 

[cicconmr]

You'd also need some kind of Error handler in the event your string is not in the cell you're looking in.

Again should be way more efficient, will take some tinkering with VBE though which is always fun

 

[Ron Coderre]

Using your posted example for testing cell N2
Perhaps something like this could work:

• Enter this list off to the side...or on another sheet (I used cells Z1:Z6).

Retire
1099
IRA
distribution
annuity
profit*sharing

• This regular formula returns searches cell N2, only up to "Source: Answer", for the search terms. If there are any matches, it returns 112:

=IF(COUNT(INDEX(SEARCH($Z$1:$Z$6,LEFT(N2,SEARCH("Source: Answer",N2)-1)),0))
,112,"")

If you prefer to "hard code" the search terms, this regular formula does the same thing:

=IF(COUNT(INDEX(SEARCH(
{"Retire";1099;"IRA";"distribution";"annuity";"profit*sharing"},
LEFT(N2,SEARCH("Source: Answer",N2)-1)),0)),112,"")

Is that something you can work with?

 

[McTracy]

Thank you so much for your help! Admittedly, I know nothing about VBE - though I may look into it if it would be a more efficient way of coding thousands of records. For now, I used Ron's formulas - creating the index of search terms on a separate sheet - and it works!

If you have time, can you explain what the formula is doing in English. I can't figure out how the COUNT, INDEX AND SEARCH are working together to pull what I need.

Just glad it works. Thanks again!!

 

[Ron Coderre]

I'll use the shorter formula to explain:
=IF(COUNT(INDEX(SEARCH($Z$1:$Z$6,LEFT(N2,SEARCH("Source: Answer",N2)-1)),0)),112,"")

In this section:
SEARCH($Z$1:$Z$6,LEFT(N2,SEARCH("Source: Answer",N2)-1))
the SEARCH function returns either a number (the position of the match) or an error (when no match can be found).
Since we are searching for multiple terms, that section returns an array of values...e.g. {#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;27}

Typically, when an array of values is returned, you'd need to complete the formula by pressing CTRL+SHIFT+ENTER (instead of just ENTER) to create an "array formula". However, in many instances, wrapping the array section in an INDEX function (with a zero as the second argument), eliminates the need to use C+S+E.

The COUNT function returns the count of numeric items in the search array. If there are no matches, the array contains all #VALUE! items and the function returns 0 (no numbers exist). But, if there are any matches, each results in a number in the array and the COUNT function will return a value greater than zero.

The IF function has an interesting and useful behavior. The first argument does NOT need to resolve to TRUE or FALSE. If it resolves to zero...it's converted to FALSE (and our formula returns a text blank ""). If it resolves to any number other than zero..it's converted to TRUE (and our formula returns 112).

I hope that helps.

 

[McTracy]

Thanks for the explanation! It always helps down the road to understand why I'm doing what I'm doing.

Not to be a PITA, but I have another question. How do I adjust the formula to search the entire cell if "source:answer" is not included in the cell? I realized after I ran the formula across the entire spreadsheet that not all cells include this terminology.

 

[Ron Coderre]

A simple approach to handle that situation is to make sure the search string exists:

=IF(COUNT(INDEX(SEARCH($Z$1:$Z$6,LEFT(N2,
SEARCH("Source: Answer",N2&"Source: Answer")-1)),0)),112,"")

Does that help?

 

[McTracy]

Works like a charm! Thank you so much for all your help!!