CODEWITHSUNDEEP
excelexcel-formula
zora
zora

Reputation: 11

Why some of the content cannot be extracted

I need to extract this from the cell For example: A1 : abode abcd=1000seconds long=50cm I need only 1000. With this sequence and this formula I can extract 1000 from the cell.

The formula I use is MID(A1, SEARCH(“=“,A1) + LEN(“=“), SEARCH(“seconds”,A1)-SEARCH(“=“,A1)-LEN(“=“))+0

However when the sequence of cell become:

A1 : long=50cmabode abcd=1000seconds

The 1000 cannot be extracted.

Please help

Upvotes: 0

Views: 39

Answers (1)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27273

Edit, since OP mentioned they are using Excel 2016, formula changed accordingly

enter image description here

• Formula used in cell D2

=SUBSTITUTE(FILTERXML("<m><b>"&SUBSTITUTE(SUBSTITUTE(B2,"="," ")," ","</b><b>")&"</b></m>","//b[contains(., 'seconds')]"),"seconds","")

Alternative Approach,

• Formula used in cell C2

=SUM(IFERROR(--TEXTAFTER(TEXTBEFORE(B2,"seconds"),"=",{1,2}),0))

Upvotes: 0

Related Questions