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


    [BFN]=IFERROR(IF(IFERROR(SEARCH("Curr:",A2,1),0)>0,LEFT(A2,IFERROR(SEARCH("-",A2,1)-1,0)),IFERROR(LEFT(A2,IFERROR(SEARCH("Non-Compliant",A2,1),0)-1),LEFT(A2,IFERROR(SEARCH("Compliant",A2,1)-1,0)))),"ERROR")[/BFN]


    In E2


    [BFN]=IFERROR(IF(IFERROR(SEARCH("curr:",A2,1)>0,0),IFERROR(MID(A2,SEARCH("/",A2,1)+2,SEARCH(")",A2,1)-2-SEARCH("/",A2,1)),0),IF(RIGHT(A2,13)="Non-Compliant","Non-Complaint","Compliant")),"ERROR")[/BFN]