Combining logic IF with string searches

[Stem sells]

Below, I entered what goes into A2, A3, and A4, respectively. They
get entered as text. Notice that only the first line of this is
what's different.

A2
- Derivatives 36 * - -
- Economics 18 * - -
- Equity Analysis 72 - - *
- Ethical & Professional Stnds. 36 - - *
- Financial Statement Analysis 108 - - *
- Fixed Income Analysis 36 - * -
- General Portfolio Management 36 * - -
- Quantitative Analysis 18 - * -

A3
- Derivatives 36 - * -
- Economics 18 * - -
- Equity Analysis 72 - - *
- Ethical & Professional Stnds. 36 - - *
- Financial Statement Analysis 108 - - *
- Fixed Income Analysis 36 - * -
- General Portfolio Management 36 * - -
- Quantitative Analysis 18 - * -

A4
- Derivatives 36 - - *
- Economics 18 * - -
- Equity Analysis 72 - - *
- Ethical & Professional Stnds. 36 - - *
- Financial Statement Analysis 108 - - *
- Fixed Income Analysis 36 - * -
- General Portfolio Management 36 * - -
- Quantitative Analysis 18 - * -

In cell b2, I'd like to print out an output which corresponds to the
"Derivatives 36" score. The score is either "* - -", "- * -", or "- -
*". If it's the first case (with the asterisks all the way to the
left), then the score is 0.40, second case is a 0.6, and finally,
0.8.

I'm aware of "=IF(ISNUMBER(SEARCH("Derivatives 36 - * -", A2))......."
as an option, but I'm having great difficulty with the syntax. I'd
basically like to create an algorithm with the following syntax/logic
for B2:B4:

=if (isnumber(search"Derivatives 36 * - -", a2)), 0.4 (if
(isnumber(search"Derivatives 36 - * -", a2)), 0.6, 0.8)

But this is syntatically wrong. Keep in mind, that I'd like to enter
0.4 if the asterisk is all the way to the left, a 0.6 if the asterisk
is in the middle, and a 0.8 if the asterisk is at the right.

Once you help me with this, then I can figure out the rest for the
entire scores (for Economics, etc.) on my own.

Please give me guidance and/or suggestions.

 

[Guest]

With your posted values in A2:A4

Try something like this:

B2: (a search term....eg Economics 18)
C2: =0.2+MATCH(SUBSTITUTE(TRIM(MID(A2,SEARCH(B2,A2)+LEN(B2),6)),"*","|"),{"|
- -","- | -","- - |"},0)*0.2

Using Economics 18 in C2
that formula returns 0.4

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

 

[Ron Rosenfeld]

Below, I entered what goes into A2, A3, and A4, respectively. They
get entered as text. Notice that only the first line of this is
what's different.

A2
- Derivatives 36 * - -
- Economics 18 * - -
- Equity Analysis 72 - - *
- Ethical & Professional Stnds. 36 - - *
- Financial Statement Analysis 108 - - *
- Fixed Income Analysis 36 - * -
- General Portfolio Management 36 * - -
- Quantitative Analysis 18 - * -

A3
- Derivatives 36 - * -
- Economics 18 * - -
- Equity Analysis 72 - - *
- Ethical & Professional Stnds. 36 - - *
- Financial Statement Analysis 108 - - *
- Fixed Income Analysis 36 - * -
- General Portfolio Management 36 * - -
- Quantitative Analysis 18 - * -

A4
- Derivatives 36 - - *
- Economics 18 * - -
- Equity Analysis 72 - - *
- Ethical & Professional Stnds. 36 - - *
- Financial Statement Analysis 108 - - *
- Fixed Income Analysis 36 - * -
- General Portfolio Management 36 * - -
- Quantitative Analysis 18 - * -

In cell b2, I'd like to print out an output which corresponds to the
"Derivatives 36" score. The score is either "* - -", "- * -", or "- -
*". If it's the first case (with the asterisks all the way to the
left), then the score is 0.40, second case is a 0.6, and finally,
0.8.

I'm aware of "=IF(ISNUMBER(SEARCH("Derivatives 36 - * -", A2))......."
as an option, but I'm having great difficulty with the syntax. I'd
basically like to create an algorithm with the following syntax/logic
for B2:B4:

=if (isnumber(search"Derivatives 36 * - -", a2)), 0.4 (if
(isnumber(search"Derivatives 36 - * -", a2)), 0.6, 0.8)

But this is syntatically wrong. Keep in mind, that I'd like to enter
0.4 if the asterisk is all the way to the left, a 0.6 if the asterisk
is in the middle, and a 0.8 if the asterisk is at the right.

Once you help me with this, then I can figure out the rest for the
entire scores (for Economics, etc.) on my own.

Please give me guidance and/or suggestions.

Two ways, both assume that your strings are exactly as you posted -- with
spaces between the - * -

=IF(ISNUMBER(SEARCH("Derivatives 36 ~* - -",A2)),0.4,
IF(ISNUMBER(SEARCH("Derivatives 36 - ~* -",A2)),0.6,
IF(ISNUMBER(SEARCH("Derivatives 36 - - ~*",A2)),0.8,"Not Found")))

=CHOOSE(SEARCH("~*",MID(A2,SEARCH("Derivatives 36",A2)
+LEN("Derivatives 36"),6))/2,0.4,0.6,0.8)
--ron

 

[Stem sells]

Ron:

Thanks for your input. The good news is that the syntax behind your
logic statement is perfect.

However, I truly think that there maybe some other error preventing
the right answer from showing correctly (I tried the first option).
I'm always getting "0.4". I think that this may have something to do
with text and numerical characters. I've seen this error pop up in
the past.

 

[Ron Rosenfeld]

Ron:

Thanks for your input. The good news is that the syntax behind your
logic statement is perfect.

However, I truly think that there maybe some other error preventing
the right answer from showing correctly (I tried the first option).
I'm always getting "0.4". I think that this may have something to do
with text and numerical characters. I've seen this error pop up in
the past.

Without seeing your formula, it's hard to know. Did you perhaps omit the tilde
(~) before each asterisk?

Does your data differ in format from that which you posted with regard to the
<spaces>? If so, and if it is variable, you might want to remove the spaces
from the data to "normalize" it.

=IF(ISNUMBER(SEARCH("Derivatives36~*--",
SUBSTITUTE(A2," ",""))),0.4,
IF(ISNUMBER(SEARCH("Derivatives36-~*-",
SUBSTITUTE(A2," ",""))),0.6,
IF(ISNUMBER(SEARCH("Derivatives36--~*",
SUBSTITUTE(A2," ",""))),0.8,"Not Found")))


--ron