Excel / Programming Question

[Vince, Pt. II]

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.

[nilodor]

So if the following were each in one cell
RDNA
DNA
RDNA DNA

Rows 2 and 3 would both be highlighted?

[Vince, Pt. II]

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

[Bobble]

Can you reasonably expect that it will be [space]DNA[space]? Can you search for "_DNA_" (where _ is a real space)?

[Scarecrow]

Would conditional formatting work?

Rule 1: Highlight all cells containing "*DNA"
Rule 2: Un-highlight all cells containing "RDNA"

[Vince, Pt. II]

Originally Posted by Bobble

Can you reasonably expect that it will be [space]DNA[space]? Can you search for "_DNA_" (where _ is a real space)?

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.

Originally Posted by Scarecrow

Would conditional formatting work?

Rule 1: Highlight all cells containing "*DNA"
Rule 2: Un-highlight all cells containing "RDNA"

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.

[Scarecrow]

How about using the dropdown filter -> Text Filters -> Does Not Contain?

[Vince, Pt. II]

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.

[Bobble]

My other suggestion would be to ask on the Excel subreddit. There's some devious excel-users on there.

[Vince, Pt. II]

Good suggestion, not sure why I haven't been over there myself. Thanks!

[Vince, Pt. II]

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.

[Vince, Pt. II]

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!