Going Crazy, need real guru

[gingerb8956]

I am building a template worksheet for use at my office where you can pull a report from my ticketing system, paste the data into one sheet, and another sheet auto populates with the data and automatically reviews it for accuracy.

One of the colums I need to automatically evaluate is a column that contains both numbers and text. The numbers are serial numbers and lead with zeros, so I had to format the column as text to keep the 0 in the 1st postion. What I need is an IF statement to return one value if any of the cells contains any number between 0-9 and then to return another value if there is no number contain in the cell.

I can't say, IF A2=0 or 1 etc, because the numbers range from 4-12 digits and each record is unique. I can't use the Search and Find functions, because if no numbers exists, I get the #VALUE error and can not build a conditional fromat from that. I've tried using =*0, *0*, "*0", "*0*", placing the zero in quotes because it is formatted as text...doesn't work.

I don't have the time to put the spreadsheet on here so I just put a few examples of what the fields look like below. I am crawling the walls, PLEASE send Excel help! Thanks.

1DJ0346D0589 I want to return a 1 for this
1FV0435W0205 I want to retunr a 1 for this
21199049384 I want to retunr a 1 for this
4013B56643 I want to retunr a 1 for this
Brooks I want to retunr a 0 for this
Chartier I want to retunr a 0 for this
Glasco I want to retunr a 0 for this
Green I want to retunr a 0 for this

 

[dangre]

This isn't the most elegant solution but it does prove to work. Assuming the cell you're evaluating is A1:

=IF(OR(COUNTIF(A1,"*0*"),COUNTIF(A1,"*1*"),COUNTIF(A1,"*2*"),COUNTIF(A1,"*3*"),COUNTIF(A1,"*4*"),COUNTIF(A1,"*5*"),COUNTIF(A1,"*6*"),COUNTIF(A1,"*7*"),COUNTIF(A1,"*8*"),COUNTIF(A1,"*9*"))=TRUE,1,0)

Let me know how it works for you.

Best of luck

 

[scifibum]

This is also not exactly elegant, and I don't know if I could explain it if I tried, but here goes:

=IF(OR(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))),1,0)

Assumes you are looking at cell A1. This would be entered as an array formula, with "CTRL-SHIFT-ENTER"

Here's my attempt to explain...

MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)

This part converts the contents of cell A1 into an array of the characters that make up the string. I'll refer to this as "[CHAR ARRAY]". If A1 contained "AB1" then [CHAR ARRAY] is equivalent to "A,B,1":

VALUE([CHAR ARRAY]) attempts to convert the array of characters to values, for the AB1 example this results in "#VALUE,#VALUE,1":

ISNUMBER(VALUE([CHAR ARRAY])) checks to see if each value in the array is a number and returns true or false. For the AB1 example this results in "FALSE,FALSE,TRUE". I'll refer to the cumulative result so far as "[TRUE/FALSE ARRAY]"

OR([TRUE/FALSE ARRAY]) returns TRUE if any of the array elements are TRUE, and FALSE if they are all FALSE. For the AB1 example it returns TRUE.

All of this is included in a simple IF statement - If the OR formula returns true, the result is 1, else the result is 0.

Enjoy.

Edit to add: I got the "MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)" part of this formula from this site: http://j-walk.com/ss/excel/eee/eee007.txt

 

[scifibum]

Sorry if this is overkill, but here is an attempt to diagram the explanation:

Book4
	A	B	C	D	E	F	G
2			[TRUE/FALSEARRAY]
3
4					[CHARARRAY]
5	{=IF(	OR(	ISNUMBER(	VALUE(	MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)	))	),1,0)}
6
Sheet1

 

[scifibum]

Better late than never - combining the COUNTIF logic that dangre provided, and an array of numbers, here's a shorter more elegant formula:

{=IF(OR(COUNTIF(A1,"*"&ROW($1:$10)-1&"*")),1,0)}

Again this is an array formula, entered with "CONTROL-SHIFT-ENTER"

 

[dangre]

Great solution Keith

Here is one more spin on your CSE formula, in the event that the record is all numeric:

=IF(OR(COUNTIF(A1,"*"&ROW($1:$10)-1&"*"),ISNUMBER(A1)),1,0)

Of course to be confirmed with Ctrl+Shift+Enter

Happy New Years!
Dan

 

[BJungheim]

Here's a non-array formula that will return 1 if there are any numbers in the cell:

=--(SUMPRODUCT(--(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A1))))>0)