Search Function

  • Thread starter Thread starter Dinesh
  • Start date Start date
D

Dinesh

Hi,

Following function formula gives me a result like"Direct FundingsDirect
Fundings". How can I modify the formula so that the result is "Direct
Fundings" once instead of layers. I know my text string has both words "VAR"
and "Intercompany" in it.

=(IF(ISERROR(SEARCH("VAR",AC81)),"","Direct
Fundings")&IF(ISERROR(SEARCH("Direct Funding",AC81)),"","Direct
Fundings")&IF(ISERROR(SEARCH("Intercompany",AC81)),"","Direct Fundings")

Thanks,
Dinesh
 
You need to flip the logic around so it only answers one time, using the &
causes the function to repeat:

=IF(ISERROR(SEARCH("VAR",E12)),
IF(ISERROR(SEARCH("Direct Funding",E12)),
IF(ISERROR(SEARCH("Intercompany",E12)),
"","Direct Funding"),"Direct Funding"),"Direct Funding")

That can DEFINITELY be shortened, but for now, that will work.
 
Try it like this:

=IF(OR(ISNUMBER(SEARCH({"VAR","Direct
Funding","Intercompany"},AC81))),"Direct Fundings","")
 
Another one that saves a couple of keystrokes:

=IF(SUM(COUNTIF(AC81,{"*VAR*","*Direct Funding*","*Intercompany*"})),"Direct
Fundings","")
 
No, Biff's formula is correct as written... he used a different approach
that doesn't require the SEARCH function. Try the two formulas he posted out
to see that they react the same.
 
Of course...those little wildcards blend into the quotes in this tiny little
forum font. good stuff!
 
Ok, this is my final answer! <g>

Saves a few more keystrokes:

=IF(COUNT(SEARCH({"VAR","Direct Funding","Intercompany"},AC81)),"Direct
Fundings","")
 
Back
Top