CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
Bala S
Bala S

Reputation: 523

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

Upvotes: 1

Views: 74

Answers (3)

JvdV
JvdV

Reputation: 75960

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+"),)

Upvotes: 4

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27438

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

enter image description here

• Formula used in cell C2

enter image description here

=--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.

Upvotes: 4

mark fitzpatrick
mark fitzpatrick

Reputation: 3322

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

Upvotes: 3

Related Questions