This is a rather complicated equation (at least to me). I am trying to
create a conditional formula that will recognize partial text with a wildcard
* and then perform mathematical function based on what is in the text else
perform a different function.

The whole text will always start with a number of 1-3 digits and then always
contain the letters "ECL" and then end with "SST" or "STM" - examples would
be: "9ECLSTM" "10.5ECLSST". OR the text will start with a number of 3 digits
and then always contain the letters "BTL-SA" - examples: "220BTL-SA" or
"210BTL-SA"

Here is the current formula:
=ROUNDDOWN(((E80-(E81*'Global settings'!$A$33))/(E79+E78)*'Global
settings'!$A$29)*E8,0)

I want to add a condition that if the text in E7 contains "*ECL*" or
"*BTL-SA", then perform the formula above, else perform this formula:
ROUNDDOWN((E80-E81)/(E79+E78)*'Global settings'!$A$29)*E8,0)

The formulas I've tried don't seem to recognize the * as I've place them.
Can someone please help me?

You could use FIND or SEARCH:

=IF(ISERR(FIND("ECL",D15)),first formula,second formula)

You'll need to modify the IF to include and OR.

--
Cheers,
Shane Devenshire


"PMo" wrote:

> This is a rather complicated equation (at least to me). I am trying to
> create a conditional formula that will recognize partial text with a wildcard
> * and then perform mathematical function based on what is in the text else
> perform a different function.
>
> The whole text will always start with a number of 1-3 digits and then always
> contain the letters "ECL" and then end with "SST" or "STM" - examples would
> be: "9ECLSTM" "10.5ECLSST". OR the text will start with a number of 3 digits
> and then always contain the letters "BTL-SA" - examples: "220BTL-SA" or
> "210BTL-SA"
>
> Here is the current formula:
> =ROUNDDOWN(((E80-(E81*'Global settings'!$A$33))/(E79+E78)*'Global
> settings'!$A$29)*E8,0)
>
> I want to add a condition that if the text in E7 contains "*ECL*" or
> "*BTL-SA", then perform the formula above, else perform this formula:
> ROUNDDOWN((E80-E81)/(E79+E78)*'Global settings'!$A$29)*E8,0)
>
> The formulas I've tried don't seem to recognize the * as I've place them.
> Can someone please help me?
>
>
>

Try something like this:

=IF(OR(ISNUMBER(FIND("ECL",E7)),ISNUMBER(FIND("BTL-
SA",E7))),first_formula,second_formula)

Hope this helps.

Pete


On Aug 26, 5:43*pm, PMo <P...@discussions.microsoft.com> wrote:
> This is a rather complicated equation (at least to me). *I am trying to
> create a conditional formula that will recognize partial text with a wildcard
> * and then perform mathematical function based on what is in the text else
> perform a different function.
>
> The whole text will always start with a number of 1-3 digits and then always
> contain the letters "ECL" and then end with "SST" or "STM" - examples would
> be: "9ECLSTM" "10.5ECLSST". *OR the text will start with a number of 3 digits
> and then always contain the letters "BTL-SA" - examples: "220BTL-SA" or
> "210BTL-SA"
>
> Here is the current formula:
> =ROUNDDOWN(((E80-(E81*'Global settings'!$A$33))/(E79+E78)*'Global
> settings'!$A$29)*E8,0)
>
> I want to add a condition that if the text in E7 contains "*ECL*" or
> "*BTL-SA", then perform the formula above, else perform this formula:
> ROUNDDOWN((E80-E81)/(E79+E78)*'Global settings'!$A$29)*E8,0)
>
> The formulas I've tried don't seem to recognize the * as I've place them.*
> Can someone please help me?

Thank you both for the quick response and great suggestions. I will give
them a try and let you know how it works out!

PMo

"Pete_UK" wrote:

> Try something like this:
>
> =IF(OR(ISNUMBER(FIND("ECL",E7)),ISNUMBER(FIND("BTL-
> SA",E7))),first_formula,second_formula)
>
> Hope this helps.
>
> Pete
>
>
> On Aug 26, 5:43 pm, PMo <P...@discussions.microsoft.com> wrote:
> > This is a rather complicated equation (at least to me). I am trying to
> > create a conditional formula that will recognize partial text with a wildcard
> > * and then perform mathematical function based on what is in the text else
> > perform a different function.
> >
> > The whole text will always start with a number of 1-3 digits and then always
> > contain the letters "ECL" and then end with "SST" or "STM" - examples would
> > be: "9ECLSTM" "10.5ECLSST". OR the text will start with a number of 3 digits
> > and then always contain the letters "BTL-SA" - examples: "220BTL-SA" or
> > "210BTL-SA"
> >
> > Here is the current formula:
> > =ROUNDDOWN(((E80-(E81*'Global settings'!$A$33))/(E79+E78)*'Global
> > settings'!$A$29)*E8,0)
> >
> > I want to add a condition that if the text in E7 contains "*ECL*" or
> > "*BTL-SA", then perform the formula above, else perform this formula:
> > ROUNDDOWN((E80-E81)/(E79+E78)*'Global settings'!$A$29)*E8,0)
> >
> > The formulas I've tried don't seem to recognize the * as I've place them.
> > Can someone please help me?
>
>