Custom Column to extract STRING

[Anonymous]

Hi Experts

How would you extract all data from column Work Type that has in the string of text the letter ABC and or BCD anywhere in the string...into a new column and all other entries are blank in the new column.. so see example below....

see image

[Stachu · Accepted solution]

try this PowerQuery code, apply it in Add Column>Custom Column window

[
abc = Text.PositionOf([Work Type],"ABC"),
bcd = Text.PositionOf([Work Type],"BCD"),
resultABC = if abc <> -1 then Text.Middle([Work Type],abc,3) else null,
resultBCD = if bcd <> -1 then Text.Middle([Work Type],bcd,3) else null,
result = Text.Combine({resultABC, resultBCD})
][result]

[dav3smthd]

Is there a way to extract a substring where I do not know the length? I know the pattern of the start but I do not know when the string I am expecting terminates. If I have to run a python script, how would I do it?

[Anonymous]

Stachu...Excellent feedback....and happy to learn also....thank you