Going Crazy, need real guru

gingerb8956

New Member
Joined
Dec 30, 2005
Messages
3
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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

dangre

Board Regular
Joined
May 27, 2002
Messages
134
This isn't the most elegant solution but it does prove to work. Assuming the cell you're evaluating is A1:

Code:
=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

Well-known Member
Joined
Jul 22, 2004
Messages
503
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

Well-known Member
Joined
Jul 22, 2004
Messages
503
Sorry if this is overkill, but here is an attempt to diagram the explanation:
Book4
ABCDEFG
2[TRUE/FALSEARRAY]
3
4[CHARARRAY]
5{=IF(OR(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))),1,0)}
6
Sheet1
 

scifibum

Well-known Member
Joined
Jul 22, 2004
Messages
503

ADVERTISEMENT

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

Board Regular
Joined
May 27, 2002
Messages
134
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

Well-known Member
Joined
Jul 24, 2004
Messages
914
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,079
Messages
5,570,082
Members
412,310
Latest member
mark884
Top