selective select
Posted by Manu on May 14, 2001 7:46 AM
I have a column with various data values(AA, 1234567, 12356-A1 etc). How can I select records with 7 digit numeric values only(e.g. 1234567)
All help is much appreciated
Posted by Kevin James on May 14, 2001 8:15 AM
Hello Manu,
Your question left me asking several questions about the format of your data and if you wanted a formula or VBA code.
If you are satisfied with using a column to display the data then....
Given:
1. Your data is in column A (insert whatever is true)
2. Your data uses row 1 for headings
3. The formula referencing column A, is in column B.
Formula for B2:
=if(len(a2)=7,a2,"")
Copy that formula down in column B to end of your data.
Kevin
Posted by Manu on May 14, 2001 8:25 AM
Thank You Kevin for your prompt reply. It worked.
Have a great day!!!!!!!!!!!!
Regards,
Manu
Posted by Manu on May 14, 2001 8:27 AM
Further to your suggestion, how can I also ensure that the values(7 digits) are only numeric??
Thanx
Manu
Posted by Kevin James on May 14, 2001 8:34 AM
Manu,
To answer your question I need more info:
If an element contains a number, is the rest of the element ALWAYS numeric or is it mixed alpha-meric?
Kevin
Posted by Manu on May 14, 2001 8:36 AM
Yes it could be alpha/numeric, spaces, characters like "-" etc..
Posted by Kevin James on May 14, 2001 8:38 AM
In that case, I don't understand your original question. Why would you want to know when a cell is all numeric?
Posted by Manu on May 14, 2001 8:45 AM
Because even though the value is all numeric, it could me more than 7 digits in length. The values cntained in this field are a terrible mix of alpha numeric, spaces and "-" like characters and I need to filter out Records with with only 7 digit numeric values.
Sorry for being long winded Kevin. I hope this explains. Thanx for your patience.
Posted by Kevin James on May 14, 2001 9:07 AM
Hi Manu,
Okay, I had a brain cramp over it but here it is:
=IF(AND(LEN(A2)=7,NOT(ISERROR(VALUE(A2)))),A2,"")
You owe me coffee. (just kidding :>)
Kevin
Posted by Manu on May 14, 2001 9:23 AM
I'll be pleased to add Donuts to that coffee. That was a brilliant and prompt effort. It worked as it should. I truly value the "net" presence of pro's such as yourself.
Thank You
Manu
Posted by Mark W. on May 14, 2001 12:21 PM
Yet, another approach...
=IF(AND(LEN(A2)=7,ISNUMBER(A2+0)),A2,"")
Posted by Mark W. on May 14, 2001 12:40 PM
A word of warning though...
The text values with an "E" (i.e., "1008E10",
"304E125") which can be entered using a prefacing
apostrophe (') will be treated as a valid,
numeric string by both Kevin's and my formula.
The only way to trap this condition would be to
test for it using...
=IF(AND(LEN(A2)=7,ISNA(MATCH("*E*",A2,0)),ISNUMBER(A2+0)),A2,"")
Posted by Kevin James on May 14, 2001 3:56 PM
Detail Oriented!
Hi Mark,
Boy, how I admire a person with so much wonderful details. That one would have completely slipped by me and if indeed such a number appeared, it would take me a while before realized it was being interpreted as an expotential number.
Can I trade brains with you?
Kevin
Posted by Mark W. on May 14, 2001 9:15 PM
Re: Detail Oriented!
Maybe not detailed enough. I read back through the
thread and noticed that Manu also allowed special
characters such as "-". A text string with a leading
"+" or "-" would also adversely affect our formulations.
So to guard against this possibility a further
revision is warranted...
=IF(AND(LEN(A2)=7,AND(ISNA(MATCH({"-*","+*","*E*"},A2,0))),ISNUMBER(A2+0)),A2,"") Hi Mark, Boy, how I admire a person with so much wonderful details. That one would have completely slipped by me and if indeed such a number appeared, it would take me a while before realized it was being interpreted as an expotential number. Can I trade brains with you? Kevin
Posted by Mark W. on May 14, 2001 9:40 PM
Oops! 2 More...
=IF(AND(LEN(A2)=7,AND(ISNA(MATCH({"* *","-*","+*","*E*","*%"},A2,0))),ISNUMBER(A2+0)),A2,"")
Now, maybe... just maybe... this is the Gold standard!
Posted by Mark W. on May 15, 2001 4:38 AM
Of course, it may be more...
straightforward to just use...
=IF(AND(LEN(A2)=7,SUM((MID(A2,{1,2,3,4,5,6,7},1)={0;1;2;3;4;5;6;7;8;9}&"")+0)=7),A2,"")
...and be done with it!! =IF(AND(LEN(A2)=7,AND(ISNA(MATCH({"* *","-*","+*","*E*","*%"},A2,0))),ISNUMBER(A2+0)),A2,"") Now, maybe... just maybe... this is the Gold standard!
