how to keep data between two vertical lines in excel sheet

Question

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.

Each line is a row in the excel.

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

Answer 1

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)))