Conditional formula containing text and wildcard

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 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 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?