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
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