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
24
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)