Formula to identify text entry in numeric data

[KayJay0618]

I have looked and cannot find anything to help. End users will be copying data with several hundred to several thousand records from an external source (to which they cannot connect) into a template I've created to help them identify keying errors in the source. The product number (which must contain exactly 9 numeric digits) must not contain alpha characters. In some cases there is appropriately a leading zero. I need a formula that will identify when an alpha character or a special character exists in the product number column. I already have the formula to identify short and long product numbers. Below is a sample. I would need to identify that row 4 has an alpha character and that row 6 has a special character. Data validation doesn't do the trick for me.

	A	B	C	D	E	F	G	H
1	ID	Cust No	CustLast	CustFirst	DA	PA	PSA	ProductNo
2	AJ0779781229	4097992	Smith	WILLIAM		E	76	140136334
3	767561	4147116	Jones	ROCHELLE	380	E	76	146132497
4	410927	4598452	Jackson	Sue	134	E	76	1460B4370
5	782299	4598452	Jackson	Sue	134	E	76	146131696
6	189336	4444486	Adams	Pago	120	E	76	14-106687
7	RM1402000056	4001248	Matthews	Amancio	134	E	76	142006190
8	RM1405000117	4001248	Matthews	Amancio	134	E	76	142040328
9	492158	4249567	Everly	Robert	134	E	76	146108574

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col></colgroup>

 

[snjpverma]

Try this in row 2 and copy down

=ISNUMBER(H2)

 

[63falcondude]

I would probably use conditional formatting for this but here is another option using a formula. Since the data will be constantly growing, I would make it into a table so that you do not have to create a dynamic range or keep changing the ranges in the formula.

Then, you can use a formula like this (dragged down as far as needed):

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	ID	Cust No	CustLast	CustFirst	DA	PA	PSA	ProductNo		Row #'s to check
2	AJ0779781229	4097992	Smith	WILLIAM		E	76	140136334		4
3	767561	4147116	Jones	ROCHELLE	380	E	76	146132497		6
4	410927	4598452	Jackson	Sue	134	E	76	1460B4370
5	782299	4598452	Jackson	Sue	134	E	76	146131696
6	189336	4444486	Adams	Pago	120	E	76	14-106687
7	RM1402000056	4001248	Matthews	Amancio	134	E	76	142006190
8	RM1405000117	4001248	Matthews	Amancio	134	E	76	142040328
9	492158	4249567	Everly	Robert	134	E	76	146108574
Sheet1

Cell Formulas
Range	Formula
J2	{=IFERROR(SMALL(IF(ISNUMBER(Table1[ProductNo])=FALSE,ROW(Table1[ProductNo])),ROWS($A$1:$A1)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[63falcondude]

Amendment to the formula in post #3:

=IFERROR(SMALL(IF(ISNUMBER(Table1[ProductNo]+0)=FALSE,ROW(Table1[ProductNo])),ROWS($A$1:$A1)),"") Ctrl Shift Enter

or

=IFERROR(SMALL(IF(ISERROR(Table1[ProductNo]+0),ROW(Table1[ProductNo])),ROWS($A$1:$A1)),"") Ctrl Shift Enter

 

[KayJay0618]

63falcondude - how would you use conditional formatting? I would prefer that.

 

[63falcondude]

63falcondude - how would you use conditional formatting? I would prefer that.

I would still format the data as a table. Then, select the "ProductNo" column (this would highlight H2:H9 in your example) > Conditional Formatting > New Rule > Use a formula

=ISERROR(H2+0)

Format: Fill color of your choice > OK

 

[KayJay0618]

Thank you. I made a slight change to ensure that blank cells were not included and it works!

 

[63falcondude]

Thank you. I made a slight change to ensure that blank cells were not included and it works!

Great! Glad to help.