Search Function

[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

 

[JBeaucaire]

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.

 

[T. Valko]

Try it like this:

=IF(OR(ISNUMBER(SEARCH({"VAR","Direct
Funding","Intercompany"},AC81))),"Direct Fundings","")

 

[T. Valko]

Another one that saves a couple of keystrokes:

=IF(SUM(COUNTIF(AC81,{"*VAR*","*Direct Funding*","*Intercompany*"})),"Direct
Fundings","")

 

[JBeaucaire]

Heh, forgot the SEARCH portion that time...

 

[Rick Rothstein]

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.

 

[JBeaucaire]

Of course...those little wildcards blend into the quotes in this tiny little
forum font. good stuff!

 

[T. Valko]

Ok, this is my final answer! <g>

Saves a few more keystrokes:

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