Hereby another implementation of FILTERXML() which I think is an absolute awesome niche function in Excel:

Formula in B1:

=IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(A1,"|","</s><s>")&"</s></t>","//s[substring(.,string-length()-2)='nCR']/following::*[1]"),)

The idea is to assert that the preceding node ends with the letters ‘nCR’ (case-sensitive). For more information about the function itself and it’s workings I’d like to forward you to this old post of mine.

I’ll also add an option to do this for users with the newest installment of Excel including access to the regex functions:

Formula in B1:

=--IFNA(REGEXEXTRACT(A1:A4,"nCR|Kd+"),)

Afaik FILTERXML() function is available from Excel 2013+ onwards therefore if one using Excel 2010 per post tags then can use the following formula as well. Refer Microsoft Documentations for more

• Formula used in cell C2

=--IFERROR(REPLACE(LEFT(A1,SEARCH("~||~",A1)-1),1,FIND("nCR|",A1)+3,),0)

• Or additionally with MS365 one can use one of the followings as well:

=IFERROR(TOCOL(--TEXTSPLIT(A1,{"nCR|","||"}),2),0)

• Or,

=--IFNA(TEXTBEFORE(TEXTAFTER(A1:A4,"nCR|"),"|"),0)

• Or, To return as one single array using the second method:

=MAP(A1:A4,LAMBDA(x,
 LET(y, TEXTSPLIT(x,{"nCR|","||"}),
         FILTER(y,1-ISERR(--y),0))))

_{NOTE: The MSFT Documentation for FILTERXML() states Excel 2016 but it is from Excel 2013+ since Excel 2010 and Excel 2013 are deprecated hence those functions which actually works from those versions are not shown in the documentation.}

You could do:

=LET( input, A1,
  s, TEXTAFTER( input, "nCR|" ),
  IF( ISNA(s), 0, TEXTBEFORE( s, "|" ) )
  )

where A1 is the text you want to process.

If you want the result to be a number (as opposed to text), do:

=LET( input, A1,
  s, TEXTAFTER( input, "nCR|" ),
  --IF( ISNA(s), 0, TEXTBEFORE( s, "|" ) )
  )

I just saw the Excel 2010 tag. OK – in that case:

= IF( ISNUMBER( FIND( "nCR", INDEX( FILTERXML("<t><s>"&SUBSTITUTE(A1, "|", "</s><s>")&"</s></t>", "//s"), 3 ) ) ),
  INDEX( FILTERXML("<t><s>"&SUBSTITUTE(A1, "|", "</s><s>")&"</s></t>", "//s"), 4 ),
  0 )

You must enter the formula as an array function: CTRL SHFT ENTER