Extract specific text based on codition from a string in a cell.

  • Hi Guys,

    I have four a list of text that is in different format, i have consolidated that into 4 types of text.

    The Column C has n number of records like this but if i have to categorize the type i can say that the lenght of the text is dynamic but the pattern is as shown below (4 patterns)

    1) Auto Close [Sample Text] - (exp: OFF / curr: OFF)
    2) Disable Test Test [Sample Test] Compliant
    3) Test Agent Startup Test - (exp: AUTO / curr: AUTO) Compliant
    4) Sample Test Data Startup Test Non-Compliant

    My Requirement is

    1) The text before the charecter ']' should be captured in Column D (D2) and the text within () to be captured in Column E (E2) but only curr: AUTO
    it also can be curr: ON, curr: OFF, but irrespective of the text after 'curr:' and before ')' should be captured.

    In this case i want to return

    Auto Close [Sample Text] (In D2) curr: OFF (In E2)

    2) Same as 1) but here i want to capture if the cell contains Compliant or Non-Compliant (exact text match)

    Disable Test Test [Sample Test] (In D3) Compliant (In E3)

    3) Same as 1). if 'curr:' is present then Compliant or Non-Compliant condition should be ignored.

    Test Agent Startup Test (In D4) curr: AUTO (In E4)

    4) Same as 2

    Sample Test Data Startup Test (In D5) Non-Compliant (In E5)

    Is this possible?

  • Re: Extract specific text based on codition from a string in a cell.

    Try the below Formulae..Let me know if that helps!

    In D2


    In E2


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!