Complex Formula Solution using Search and IF

[XP]

I am using Office 2003 on Windows XP.

I need a spreadsheet formula I can write into the sheet from code that would
use the following logic, but I'm stumped. I would like to do this using a
single formula because it is really for another user and their sheet
structure demands it:

IF G6 LIKE "*FUTURE*" Then E6 * N6 * 10000
IF G6 LIKE "*SWAP*" Then E6 * N6 * 2500
IF G6 NOT LIKE "*FUTURE*" NOR LIKE "*SWAP*" Then blank (i.e. "")

I know in place of "LIKE" I need to use "Search", but I can't crack the
logic. Could someone please post a solution?

Thanks much in advance.

[Bob Phillips]

=IF(ISNUMBER(SEARCH(G6,"FUTURE")),E6*N6*10000,IF(ISNUMBER(SEARCH(G6,"SWAP"))
,E6*N6*2500,""))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"XP" <[email protected]> wrote in message
news:[email protected]...
> I am using Office 2003 on Windows XP.
>
> I need a spreadsheet formula I can write into the sheet from code that
would
> use the following logic, but I'm stumped. I would like to do this using a
> single formula because it is really for another user and their sheet
> structure demands it:
>
> IF G6 LIKE "*FUTURE*" Then E6 * N6 * 10000
> IF G6 LIKE "*SWAP*" Then E6 * N6 * 2500
> IF G6 NOT LIKE "*FUTURE*" NOR LIKE "*SWAP*" Then blank (i.e. "")
>
> I know in place of "LIKE" I need to use "Search", but I can't crack the
> logic. Could someone please post a solution?
>
> Thanks much in advance.

[XP]

Thanks Bob, but I need wild card searching for both FUTURE and SWAP. For
example, the cell could contain: "EOM Swap" or "Swing Swap", etc. Your
formula works for "Swap" but not if the cell contains other characters...

Can you please help be a bit further?

"Bob Phillips" wrote:

> =IF(ISNUMBER(SEARCH(G6,"FUTURE")),E6*N6*10000,IF(ISNUMBER(SEARCH(G6,"SWAP"))
> ,E6*N6*2500,""))
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "XP" <[email protected]> wrote in message
> news:[email protected]...
> > I am using Office 2003 on Windows XP.
> >
> > I need a spreadsheet formula I can write into the sheet from code that
> would
> > use the following logic, but I'm stumped. I would like to do this using a
> > single formula because it is really for another user and their sheet
> > structure demands it:
> >
> > IF G6 LIKE "*FUTURE*" Then E6 * N6 * 10000
> > IF G6 LIKE "*SWAP*" Then E6 * N6 * 2500
> > IF G6 NOT LIKE "*FUTURE*" NOR LIKE "*SWAP*" Then blank (i.e. "")
> >
> > I know in place of "LIKE" I need to use "Search", but I can't crack the
> > logic. Could someone please post a solution?
> >
> > Thanks much in advance.
>
>
>

[XP]

Sorry for my previous post, I should not have doubted you, I had a typo. It
works great, thanks again!!!

"Bob Phillips" wrote:

> =IF(ISNUMBER(SEARCH(G6,"FUTURE")),E6*N6*10000,IF(ISNUMBER(SEARCH(G6,"SWAP"))
> ,E6*N6*2500,""))
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "XP" <[email protected]> wrote in message
> news:[email protected]...
> > I am using Office 2003 on Windows XP.
> >
> > I need a spreadsheet formula I can write into the sheet from code that
> would
> > use the following logic, but I'm stumped. I would like to do this using a
> > single formula because it is really for another user and their sheet
> > structure demands it:
> >
> > IF G6 LIKE "*FUTURE*" Then E6 * N6 * 10000
> > IF G6 LIKE "*SWAP*" Then E6 * N6 * 2500
> > IF G6 NOT LIKE "*FUTURE*" NOR LIKE "*SWAP*" Then blank (i.e. "")
> >
> > I know in place of "LIKE" I need to use "Search", but I can't crack the
> > logic. Could someone please post a solution?
> >
> > Thanks much in advance.
>
>
>

[XP]

Just a comment, interesting use of "IsNumber" rather than "IsErr" which is
what I was trying to use. "IsNumber" simplifies it a lot, I like it. Thanks!!!

"XP" wrote:

> Sorry for my previous post, I should not have doubted you, I had a typo. It
> works great, thanks again!!!
>
> "Bob Phillips" wrote:
>
> > =IF(ISNUMBER(SEARCH(G6,"FUTURE")),E6*N6*10000,IF(ISNUMBER(SEARCH(G6,"SWAP"))
> > ,E6*N6*2500,""))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "XP" <[email protected]> wrote in message
> > news:[email protected]...
> > > I am using Office 2003 on Windows XP.
> > >
> > > I need a spreadsheet formula I can write into the sheet from code that
> > would
> > > use the following logic, but I'm stumped. I would like to do this using a
> > > single formula because it is really for another user and their sheet
> > > structure demands it:
> > >
> > > IF G6 LIKE "*FUTURE*" Then E6 * N6 * 10000
> > > IF G6 LIKE "*SWAP*" Then E6 * N6 * 2500
> > > IF G6 NOT LIKE "*FUTURE*" NOR LIKE "*SWAP*" Then blank (i.e. "")
> > >
> > > I know in place of "LIKE" I need to use "Search", but I can't crack the
> > > logic. Could someone please post a solution?
> > >
> > > Thanks much in advance.
> >
> >
> >

[Bob Phillips]

You would have to use NOT(ISERR as you want to find a non-error.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"XP" <[email protected]> wrote in message
news:[email protected]...
> Just a comment, interesting use of "IsNumber" rather than "IsErr" which is
> what I was trying to use. "IsNumber" simplifies it a lot, I like it.
Thanks!!!
>
> "XP" wrote:
>
> > Sorry for my previous post, I should not have doubted you, I had a typo.
It
> > works great, thanks again!!!
> >
> > "Bob Phillips" wrote:
> >
> > >
=IF(ISNUMBER(SEARCH(G6,"FUTURE")),E6*N6*10000,IF(ISNUMBER(SEARCH(G6,"SWAP"))
> > > ,E6*N6*2500,""))
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "XP" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I am using Office 2003 on Windows XP.
> > > >
> > > > I need a spreadsheet formula I can write into the sheet from code
that
> > > would
> > > > use the following logic, but I'm stumped. I would like to do this
using a
> > > > single formula because it is really for another user and their sheet
> > > > structure demands it:
> > > >
> > > > IF G6 LIKE "*FUTURE*" Then E6 * N6 * 10000
> > > > IF G6 LIKE "*SWAP*" Then E6 * N6 * 2500
> > > > IF G6 NOT LIKE "*FUTURE*" NOR LIKE "*SWAP*" Then blank (i.e. "")
> > > >
> > > > I know in place of "LIKE" I need to use "Search", but I can't crack
the
> > > > logic. Could someone please post a solution?
> > > >
> > > > Thanks much in advance.
> > >
> > >
> > >