I need to search column A and find one of 2 words at the start of text in each cell in column A, then copy that cell over to column B without either of those 2 words.
I've looked into SUMPRODUCT, SUM, COUNTIF, SEARCH, and MID but can't find a mixture of any that would do what I need.
Best answer
It's a long formula, but the gist is that we check the left-most characters for the first string, if it is found, take the right-most characters minus the length of our target string. If it's not found, then check the next word and do the same thing. Finally, if neither is found return a blank. This all assumes that only one of your two words is in the string and that it is at the beginning of the string. Based on your description of the problem I think that is the case. But if not, you'll need to tweak this a bit. Here is the formula for cell B2:
=IF(LEFT(UPPER(A2), LEN("TestWord"))=UPPER("TestWord"), RIGHT(A2, LEN(A2)-LEN("TestWord")),IF(LEFT(UPPER(A2), LEN("OtherTestWord"))=UPPER("OtherTestWord"), RIGHT(A2, LEN(A2)-LEN("OtherTestWord")),""))