Display the number from <p>24</p>-CodePudding

I was hoping someone can help me, i have written this formula

=VLOOKUP(A2,'Inactivity Report'!A:B,2,0)

It will return the result

which is correct, but I just wanted it to display the number so I wondered how I can incorporate the MID formula into this formula.

Table 1

| Stock Ticker | Number       |
| ---          | ---.         |
| TSO          | <p>4239</p>  |
| PPL          | <p>24</p>    |
| MRO          | <p>244</p>   |
| CCL          | <p>82</p>    |
| HOLX         | <p>1</p>     |
| PYPL         | <p>146</p>   |
| CINF         | <p>76</p>    |
| WFM          | <p>198</p>   |
| PH           | <p>112233</p>|
| URI          | <p>88</p>    |

Table 2

| Stock Ticker | Number I want |
| ---          | ---           |
| TSO          | 4239          |

CodePudding user response：

Using FILTERXML() Function,

Use any one of the either based on your Excel Version.

• Formula used in cell E2

=FILTERXML(VLOOKUP(D2,A$2:B$11,2,0),"//p[1]")

• Formula used in cell F2

=FILTERXML(XLOOKUP(D2,A$2:A$11,B$2:B$11),"//p[1]")

CodePudding user response：

For non-Windows environment Office365:

=LET(res,VLOOKUP(A2,'Inactivity Report'!A:B,2,0),
     p,FIND("<p>",res) 3,
     pp,FIND("</p>",res),
     MID(res, p,pp-p))

Or older Excel versions:

=MID(VLOOKUP(A2,'Inactivity report'!A:B,2,0),
     FIND("<p>",VLOOKUP(A2,'Inactivity report'!A:B,2,0)) 3,
     FIND("</p>",VLOOKUP(A2,'Inactivity report'!A:B,2,0))
    -FIND("<p>",VLOOKUP(A2,'Inactivity report'!A:B,2,0))-3)