how to keep data between two vertical lines in excel sheet-CodePudding

I am trying to exclude the data before and after the vertical line | and keep only the data between them. Since I need to keep only the data between the first vertical line | and second vertical line |, if I use *| in the find column and click on the replace all, it removing the data in the middle (doing opposite of what I need). Any help would be appreciated on how to approach this.

Sample data in excel:

san-diego-22 | AWS clusters1 | P-12313123
america.ls.office | kafka version is not matched | P-34522
[email protected] | VM is not responding | P-123123
projects/[email protected]/accounts/iap-232434.com/vault/3f4s234234234wd2342342

Output

AWS clusters1
kafka version is not matched
VM is not responding
projects/[email protected]/accounts/iap-232434.com/vault/3f4s234234234wd2342342

Thank you

CodePudding user response：

This would do what you want:

=IFERROR(
    SUBSTITUTE(
        SUBSTITUTE(
            A1,
            LEFT(A1,FIND("|",A1) 1),
            ""),
        RIGHT(
            SUBSTITUTE(
                A1,
                LEFT(A1,FIND("|",A1) 1),
                ""),
            LEN(SUBSTITUTE(A1,
                LEFT(A1,
                FIND("|",A1) 1),""))- 
                FIND("|",
                    SUBSTITUTE(A1,                    
                        LEFT(A1,FIND("|",A1) 1),
                        "")) 2),
    ""),
 A1)

In Office 365 you would be able to spill it with:

=BYROW(A1:A4, LAMBDA(r,IFERROR(INDEX(TEXTSPLIT(r,"|"),,2),r)))

CodePudding user response：

Perhaps, you could try:

• Formula used in cell B1 --> MS365 Users Only

=IFERROR(INDEX(TEXTSPLIT(TEXTJOIN("*",,A1:A4)," | ","*"),,2),A1:A4)

• Formula used in cell B15 --> MS365 Users Only

=BYROW(A1:A4,LAMBDA(x,IFERROR(TEXTBEFORE(TEXTAFTER(x," | ")," | "),x)))

• Formula used in cell B8 --> Excel 2010 Onwards

=IF(ISNUMBER(FIND(" | ",A1)),TRIM(MID(SUBSTITUTE(A1," | ",REPT(" ",100)),100,100)),A1)

Note: The first 2 formulas will spill, hence no need to drag while the second one you need to fill down.