IF Formula to find asterisks

[jondavis1987]

I'm trying to make an IF formula to detect if there's any asterisks in the worksheet.

 =IF(WORKSHEET RANGE," * DOES NOT MEET SPECIFICATIONS ","")

That way if there is one it will display *DOES NOT MEET SPECIFICATIONS" and if there isn't one it displays nothing.

 

[Scott T]

Try

=IF(SUM(--ISNUMBER(SEARCH("~*",A1:L17)))>0," * DOES NOT MEET SPECIFICATIONS ","")

Enter with CTRL+SHIFT+ENTER not just enter. If done correctly the formula will have {} around it in the formula bar.

 

[jondavis1987]

It doesn't seem to be working.

=IF(ISNUMBER(SEARCH("*",A15:AF49)), "DOES NOT MEET SPECIFICATIONS", "")

I got this code to work if the asterisk is in a cell by itself. The code underneath is one of the cells that can have an asterisk pop up.

=IF(A!D10=100,A!D10,A!D10&"   *")

So if it has an asterisk it will generate this for a cell value 99 *

 

[AhoyNC]

Basically the same formula that Scott T gave you, but if you use SUMPRODUCT instead of SUM you can just use ENTER. No need for CTRL-SHIFT-ENTER.
Adjust range to meet your needs.

Excel Workbook
	A	B	C	D
1	rt.			* DOES NOT MEET SPECIFICATIONS
2
3
4	ui	uio
5		ty
6	jku *
Sheet

 

[jondavis1987]

That Seemed to do it. I had the array formula on there but for some reason it didn't find anything. Thank you!

 

[jtakw]

Hi,

Non array and a bit simpler, normally entered:

Book1
	A	B	C	D
1	rt.			* DOES NOT MEET SPECIFICATIONS
2		*abc
3	*
4	ui	uio
5		ty
6	jku *
Sheet660

Cell Formulas
Range	Formula
D1	=IF(COUNTIF(A1:B6,"*~**")," * DOES NOT MEET SPECIFICATIONS ","")

 

[jondavis1987]

Thank you!