excelexcel-formulaexcel-2010

Find particular word in excel and fetch values

Below are my Excel rows. I want to get values after nCR. Kidnly provide functions for the same.

"\r\nAS|01|9999999\r\nCR|1234560||result"

"\r\nAS|01|9999999\r\nCR|7601231||Baba"

"\r\nAS|01|123\r\nCR|123456||tEST"

"\r\nAS|01|123\r\nBR|123456||tEST"

Result:

1234560

7601231

123456

0

Solution

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

    enter image description here

    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:

    enter image description here

    Formula in B1:

    =--IFNA(REGEXEXTRACT(A1:A4,"nCR\|\K\d+"),)