02-12-2018, 10:37 AM | #1 | ||
Pro Starter
Join Date: Sep 2009
Location: Somewhere More Familiar
|
Excel / Programming Question
Is there a way to use a wildcard character in Excel (or otherwise) that excludes a specific instance? For example, I want to find and highlight any instances of "*DNA" within variable text blocks that are not "RDNA". Unfortunately, these text blocks are manually entered by people who do not follow directions and are allergic to grammar, punctuation, and common sense...and they can include both "RDNA" and "DNA" - which renders a not("RDNA") function moot because it will ignore legit entries of DNA if they also include RDNA.
|
||
02-12-2018, 02:30 PM | #2 |
College Benchwarmer
Join Date: Oct 2000
Location: calgary, AB
|
So if the following were each in one cell
RDNA DNA RDNA DNA Rows 2 and 3 would both be highlighted? |
02-12-2018, 04:52 PM | #3 |
Pro Starter
Join Date: Sep 2009
Location: Somewhere More Familiar
|
Assuming each of those rows was one cell, yes. I think my only bet would be to replace all instances of "RDNA" with "" and then to re-filter for DNA, but I'm not sure I can do that without a manual process. Ideally this all happens behind the scenes (this is a Web Intelligence report spitting out an Excel sheet).
|
02-13-2018, 06:44 AM | #4 |
College Prospect
Join Date: Nov 2006
Location: High and outside
|
Can you reasonably expect that it will be [space]DNA[space]? Can you search for "_DNA_" (where _ is a real space)?
|
02-13-2018, 08:17 AM | #5 |
College Prospect
Join Date: Oct 2000
Location: The Flatlands of America
|
Would conditional formatting work?
Rule 1: Highlight all cells containing "*DNA" Rule 2: Un-highlight all cells containing "RDNA"
__________________
Post Count: Eleventy Billion - so deal with it! |
02-13-2018, 08:38 AM | #6 | ||
Pro Starter
Join Date: Sep 2009
Location: Somewhere More Familiar
|
Quote:
No. Due to weirdness with WEBI, a wildcard character requires there to actually BE a character there, so if the cell STARTS with "DNA" it won't be picked up because there is no character there. Similarly, assuming a space means you lose any "/DNA" "-DNA" or "(DNA)" type entries, which are legit. Quote:
Similar to the NOT logic, this would then un-highlight cells that contain both "RDNA" and "DNA." Seems like such a simple problem, but it's been a tricky one. My stop-gap solution thus far has been very similar to Bobble's suggestion: I highlight anything with " DNA" and then manually scrub the rest. It's just foolishly time consuming on a weekly basis, and I know I can make it better. Last edited by Vince, Pt. II : 02-13-2018 at 08:38 AM. |
||
02-13-2018, 08:48 AM | #7 |
College Prospect
Join Date: Oct 2000
Location: The Flatlands of America
|
How about using the dropdown filter -> Text Filters -> Does Not Contain?
__________________
Post Count: Eleventy Billion - so deal with it! |
02-13-2018, 09:05 AM | #8 |
Pro Starter
Join Date: Sep 2009
Location: Somewhere More Familiar
|
I'll play with it, but I think it will have the same constraints. For example, if I have the following text examples in three cells:
"XXXX RDNA @0934, DNA @0936" "XXXX DNA @1031" "XXXX RDNA @0934" I want cells 1 & 2 highlighted/included and cell 3 ignored. The drop down filter would filter out everything but cell 2, because "does not contain RDNA" evaluates to false for 1 & 3. Since there is ALSO an instance of DNA in cell 1, however, I want this cell to be highlighted/included. |
02-13-2018, 10:32 AM | #9 |
College Prospect
Join Date: Nov 2006
Location: High and outside
|
My other suggestion would be to ask on the Excel subreddit. There's some devious excel-users on there.
|
02-13-2018, 12:34 PM | #10 |
Pro Starter
Join Date: Sep 2009
Location: Somewhere More Familiar
|
Good suggestion, not sure why I haven't been over there myself. Thanks!
|
02-13-2018, 12:51 PM | #11 |
Pro Starter
Join Date: Sep 2009
Location: Somewhere More Familiar
|
Great success! If I replace all "RDNA" instances in each cell (currently replacing with "----"), then re-screen for "DNA" I get the results I am looking for.
Last edited by Vince, Pt. II : 02-13-2018 at 12:52 PM. |
02-13-2018, 12:54 PM | #12 |
Pro Starter
Join Date: Sep 2009
Location: Somewhere More Familiar
|
An example of what I mean based upon my previous example.
Original text: "XXXX RDNA @0934, DNA @0936" "XXXX DNA @1031" "XXXX RDNA @0934" After replacement: "XXXX ---- @0934, DNA @0936" "XXXX DNA @1031" "XXXX ---- @0934" Now safe to search for DNA! |
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
Thread Tools | |
|
|