## How to not count cell if cell contains sub-string

I have the following formula to count cells within a range B10:B1095 which do not contain certain strings

=COUNTIFS(   B10:B1095, "<>",   B10:B1095, "<>Manual",   B10:B1095, "<>TODO") 

How can I add an additional condition to check if the cells in a range do not contain a specific sub-string?

## Excel 365 – Pivot Table – show all columns with particular substring in column header

I’d like to sort my data by column substring but I don’t know how to do it with pivot tables.

I’m trying to sort my grades by assignment type and then make calculations on that subset of data. The first column is the student’s username and then all other columns contain pass/fail data for each assignment. There are three different types of assignments PQ, RQ , and WQ (proficiency quiz, reading quiz, and weekly quiz), so each column’s header starts with either PQ, RQ, or WQ. I’d like to make a pivot table that shows all PQs so that I can view their progress with that assignment type as well as find the average pass/fail rate for each student to calculate total grades. This also needs to happen dynamically so that if add more PQs throughout the semester the table will reflect that when I refresh it.

Here is an image of my sample data: Sample Data RAW

Here is an image of the sorts of subsets I’d like to see: Subset of sample data that I’d like to see

(Sorry I can’t embed the images, this is my first post.)

If pivot tables aren’t the way to do this, another approach would be great to know.

## Write a program that prints the longest substring in which the letters occur in alphabetical order by ascii

Write a program that prints the longest substring of s in which the letters occur in alphabetical order. And the output has to be: Longest substring in alphabetical order is: beggh

here is my code. I know that it is awful to ask somebody to write code for someone, but I have no idea how to continue it. Help please

s = 'azcbobobegghakl' characters = ""  for num in range(len(s)):     if num == len(s) - 1:         break     elif ord(s[num]) <= ord(s[num + 1]):         characters += s[num]     elif ord(s[num]) >= ord(s[num - 1]):         characters += s[num] 

## Elegant way to replace substring in a regex with optional groups in Python?

Given a string taken from the following set:

strings = [     "The sky is blue and I like it",     "The tree is green and I love it",     "A lemon is yellow" ] 

I would like to constuct a function which replaces subject, color and optional verb from this string with others values.

All strings match a certain regex pattern as follow:

regex = r"(?:The|A) (?P<subject>\w+) is (?P<color>\w+)(?: and I (?P<verb>\w+) it)?" 

The expected output of such function would look like this:

repl("The sea is blue", "moon, "white", "hate") # => "The moon is white" 

Here is the solution I come with (I can’t use .replace() because there is edge cases if the string contains the subject twice for example):

def repl(sentence, subject, color, verb):     m = re.match(regex, sentence)     s = sentence     new_string = s[:m.start("subject")] + subject + s[m.end("subject"):m.start("color")] + color     if m.group("verb") is None:         new_string += s[m.end("color"):]     else:         new_string += s[m.end("color"):m.start("verb")] + verb + s[m.end("verb"):]     return new_string 

Do you think there is a more straightforward way to implement this?

## find substring with count and return even frequency substring list

I have one following question and write a function for it. can anyone give me some suggestion how my code can be better and better way to handle to build up return list with even frequency?

One message system contains two device type message that each message is formatted with device type identifier with device id and message count. Write a function to parse the message based on device type and message count and return a list with each device id in even message frequency
rule: input string: message
device identifier
1:iOS device ID identifier: start with ‘I’ following with 3 character, total length is 4 character
2:Android device ID identifier: start with ‘A’ following with 2 character, total length is 3 character
3:message count is following by device id until next device ID
ex: input: Asq2: {‘Asq’: 2} Asq with 2 message count
output: [‘Asq’, ‘Asq’]

input: Akb2IAld3: ID: {‘Akb’: 2, ‘IAld2’: 3} Akb with 2 message count, IAld with 3 message count
output: [‘Akb’, ‘IAld’, ‘Akb’, ‘IAld’, ‘IAld’]

input: Aqp1Iasd2Aqp4IAbd1: {‘Aqp’: 5, ‘Iasd’: 2, ‘IAbd’: 1}
output: [‘Aqp’, ‘Iasd’, ‘IAbd’, ‘Aqp’, ‘Iasd’, ‘Aqp’, ‘Aqp’, ‘Aqp’]

from typing import List   def parse_message(string) -> List:     i, j, ids_map, n, ids = 0, 0, dict(), len(string), ''      while i < n:         if string[i] in ('I', 'A') or i == n - 1:             if ids:                 if i == n - 1:                     ids_map[ids] = ids_map.get(ids, 0) + int(string[j:])                 else:                     ids_map[ids] = ids_map.get(ids, 0) + int(string[j:i])             j = i + 4 if string[i] == 'I' else i + 3             ids = string[i:j]             i = j - 1         i += 1     res = []     while any(i > 0 for i in ids_map.values()):         for k, v in ids_map.items():             if v > 0:                 res.append(k)                 ids_map[k] -= 1     return res  

## How to keep a substring and delete rest of string using Python

I am beginner in Python and I am web scraping a webpage and I get the following most of the time with the word “available” always there.

Your   product is    available   and costs 10.00€ 

I cannot count the empty spaces and remove word by word, so how can I keep only the word available and delete the rest of it?

And have something like

Available

thank you

## Longest common substring in linear time

We know that the longest common substring of two strings can be found in O(N^2) time complexity. Can a solution be found in only linear time?

## How do I remove a substring from system_profiler with a posible if then?

In short, I’m writing a script that will check if a computer is a MacBook Pro or a MacBook Air and then I want to use the output as a variable.

Using

system_profiler SPHardwareDataType | grep “Model Name” 

I can get Model Name: MacBook Pro but all I need is either “MacBook Pro” or “MacBook Air”

-oP in grep gives me an error. Another idea was to use find but I’m unsure how I’d use find if I’m not searching an actual file.

## Find the smallest set of strings which “covers” a given set of strings (coverage = containing as substring)

Let $$S$$ be a finite set of strings and $$0 < k\leq l$$ integers. We want to find the smallest set of strings $$T(k,l)$$ for which the following holds:

• $$\forall t \in T(k,l): k \leq |t| \leq l$$
• $$\forall s \in S \ \exists t \in T(k,l): t \subset s$$ (meaning: $$s$$ is containing $$t$$ as a subset).

I would appreciate any help regarding this problem. I see that I could generate the set of all strings with length between $$k$$ and $$l$$ then consider the power set but I think there must be a better way to solve this.

I’m not even sure how hard this problem is (I think there may be a reduction for the set cover problem which means it is $$NP$$-complete but I really don’t know) or if there are any good approximation (or maybe exact) algorithms available.

## Efficient substring match

I have a list of unique strings (approx, 25,00,000) of different lengths and I am trying to find if there is any string which occurs as a substring of previous strings.

  def index_containing_substring(the_list, substring):     for i, s in enumerate(the_list):         if substring in s:             return i     return -1  def string_match():     test_list=['foo bar abc xml','fdff gdnfgf gdkgf','foo bar','abc','xml','xyz']     max_len=4 # I am storing the maximum length of sentence     # the list starts with reverse order     # i.e sentence with highest length are at the top     safe_to_add=[]     for s in test_list:         if len(s)==max_len:             safe_to_add.append(s)         else:             idx=index_containing_substring(safe_to_add,s)             if idx==-1:                 safe_to_add.append(s)             else:                 # process the substring                 print('match found {} for {}'.format(test_list[idx],s)) 

This method works fine but I think it is pretty slow. Is there a better way to solve this problem using a better data structure (trie or suffix tree)?

Output

match found foo bar abc xml for foo bar match found foo bar abc xml for abc match found foo bar abc xml for xml