Hi all,
How can I, when I use the search - possibility (ctrl-F) use multiple criteria
eg: I want to get every cell with contents ???.?? but not with the contents ???.? (both based on the contents of the entire cell)
Is this possible?
Thanks in advance
Hein
Last edited by Hein; 02-19-2014 at 04:52 PM.
???.??
if you use that as your search criteria on whole cell contents it matches just that pattern.... or do you mean something else
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
No that's what I mean
This is just an example. I have more cases in which this is usefull
eg: I want 123.45 but not 123.4
Hein
Are those numbers or text values?
Pete
works for me with either but it must be an exact match it wouldn't find for example 123.567 and the cell is formatted to 2 decimal places so it looks like 123.57
Last edited by martindwilson; 02-19-2014 at 03:26 PM.
Hi Martin,
Thanks for your solution, but .. I got this far.
I was more thinking of a solution that looks like If (A1 = "ABC.DE"and Not Like "ABC.D") or like IF(A1 = "Yesterday"and Not Like "Yes")
Is this possible?
If I can do this it would give me a lot more flexibility to search for items instead of trying every possible variation.
Hain
im not sure where you ar going with this
but you can use something like
=ISNUMBER(SEARCH(" ???.?? "," "&B1&" ")) to see if its anywhere in a cell or surrounded by spaces
=AND(LEN(B1)+2=8,ISNUMBER(SEARCH(" ???.?? "," "&B1&" ")))
for a cell only containing the pattern
I have tried your suggestion (hopefully with all correct quotes etc) but I don't think I can use the search option with a celladdress in it. I think (IMHO) that Excel searches for that exact text.
In the meantime (why didn't I think of that earlier?) I think I may have a solution:
I will write a macro that searches the entire worksheet (50000+ rows and 15 or so columns) and that searches for the cells that have the contents I am looking for (a macro gives me a lot of opportunities to define a string I am looking for)
If a cell is found that matches the criteria I will make it, eg, yellow.
As a 2nd step I can search for all yellow cells (using the ctrl-F option)
It is a lot more work but writing this macro with inputboxes is a 1-time job that I can use multiple times.
May I thank you for your trouble and time, it was most helpful, your suggestions put me on the right track
Hein
Hi HeinOriginally Posted by Hein
i am not an expert but what i would do is the below
Ctrl+F you will get a search box, go to options, select "Match entire cell contents"
here you can find 123.41 instead of 123.4 or ???.? instead of ??.?
regards
Mike
Mike, thanks for the input (Did you notice this thread is almost 8 years old though? I have done the same thing myself before lol)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks