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.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Dinesh" wrote:

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

Try it like this:

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

--
Biff
Microsoft Excel MVP


"Dinesh" wrote in message
...
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

Another one that saves a couple of keystrokes:

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try it like this:

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

--
Biff
Microsoft Excel MVP


"Dinesh" wrote in message
...
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

Heh, forgot the SEARCH portion that time...
--
"Actually, I *am* a rocket scientist." -- JB

"T. Valko" wrote:

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.

--
Rick (MVP - Excel)


"JBeaucaire" wrote in message
...
Heh, forgot the SEARCH portion that time...
--
"Actually, I *am* a rocket scientist." -- JB

"T. Valko" wrote:

Another one that saves a couple of keystrokes:

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

Of course...those little wildcards blend into the quotes in this tiny little
forum font. good stuff!
--
"Rick Rothstein" wrote:

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.

Ok, this is my final answer! <g

Saves a few more keystrokes:

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

--
Biff
Microsoft Excel MVP


"JBeaucaire" wrote in message
...
Of course...those little wildcards blend into the quotes in this tiny
little
forum font. good stuff!
--
"Rick Rothstein" wrote:

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.