Why can't I use * in formula?

gresple · May 8th, 2002, 07:34 PM

Hello guys, I would like to set a formula to search the remark field that contains the word "pass", but I found out I could not use * in the IF formula, can anyone help me?

James · May 8th, 2002, 07:38 PM

Hi,

"*" is Excels multiplication symbol?

James

fdrl · May 8th, 2002, 08:56 PM

Quote:

On 2002-05-08 18:34, gresple wrote:
Hello guys, I would like to set a formula to search the remark field that contains the word "pass", but I found out I could not use * in the IF formula, can anyone help me?

The wild card character * for any sequence of characters (or ? for any single character) can be used in the SEARCH funtion.
What are you trying to do?

Yogi Anand · May 8th, 2002, 09:19 PM

Quote:

On 2002-05-08 18:34, gresple wrote:
Hello guys, I would like to set a formula to search the remark field that contains the word "pass", but I found out I could not use * in the IF formula, can anyone help me?

No, to my knowledge, you can not use a wildcard in the IF function.

Regards!

Mark W. · May 9th, 2002, 10:51 AM

Provide a precise problem statement, sample data, and expected results.

klb · May 9th, 2002, 11:16 AM

Try:

=IF(ISERR(FIND("PASS",C4,1)>0),"your message or action if pass is not found","your message if pass is found")
This looks for the text PASS in C4 beginning a the first character and returns the position in the string where it found it. It returns an error if it is not found, thus the ISERR

You can remove the quotes is you want a number entered in the cell instead.

Forgot to mention that the "PASS" is case-sensitive.

[ This Message was edited by: klb on 2002-05-09 10:17 ]

Yogi Anand · May 9th, 2002, 05:48 PM

Forgot to mention that the "PASS" is case-sensitive.

Hi klb:
Just to extend your point a little further, one can use the SEARCH function instead of FIND -- since SEARCH is not case sensitive.

Regards!

gresple · May 9th, 2002, 06:39 PM

Thank you very much klb!

May 9th, 2002, 05:48 PM	#7
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Forgot to mention that the "PASS" is case-sensitive. Hi klb: Just to extend your point a little further, one can use the SEARCH function instead of FIND -- since SEARCH is not case sensitive. Regards! __________________ Regards! Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC www.energyefficientbuild.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 8th, 2002, 07:34 PM	#1
gresple New Member Join Date: Apr 2002 Location: Hong Kong Posts: 5	Hello guys, I would like to set a formula to search the remark field that contains the word "pass", but I found out I could not use * in the IF formula, can anyone help me?

May 8th, 2002, 07:38 PM	#2
James Board Regular Join Date: Feb 2002 Location: Huntington Beach, CA USA Posts: 327	Hi, "*" is Excels multiplication symbol? James

May 9th, 2002, 10:51 AM	#5
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Provide a precise problem statement, sample data, and expected results.

May 9th, 2002, 11:16 AM	#6
klb Board Regular Join Date: Apr 2002 Location: Minnesota Posts: 821	Try: =IF(ISERR(FIND("PASS",C4,1)>0),"your message or action if pass is not found","your message if pass is found") This looks for the text PASS in C4 beginning a the first character and returns the position in the string where it found it. It returns an error if it is not found, thus the ISERR You can remove the quotes is you want a number entered in the cell instead. Forgot to mention that the "PASS" is case-sensitive. [ This Message was edited by: klb on 2002-05-09 10:17 ]

May 9th, 2002, 06:39 PM	#8
gresple New Member Join Date: Apr 2002 Location: Hong Kong Posts: 5	Thank you very much klb!