Forum Discussion
Solved
Excel Extract Using Find and Replace Function
I'm working on a spreadsheet that has a string of text: ITRM MODEL(INPUT:1404)(LOAD:210.555) ITRM MODEL(INPUT:4012)(LOAD:1315.39) ITRM MODEL(INPUT:0)(LOAD:0) I need to extract the la...
Megan1004 FILTERXML() would be best practice in this case. Use below formula to extract last node after colon. Try-
=FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,":","</s><s>"),")","")&"</s></t>","//s[last()]")And if you are interested go with REPLACE() then use SUBSTITUTE() function to remove last bracket.
=SUBSTITUTE(REPLACE(A1,1,FIND(":",A1,18),""),")","")And if you are an use of Microsoft 365 insiders or Current Preview channel then can try TEXTAFTER() function.
=TEXTBEFORE(TEXTAFTER(A1,"LOAD:"),")")
Megan1004 FILTERXML() would be best practice in this case. Use below formula to extract last node after colon. Try-
=FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,":","</s><s>"),")","")&"</s></t>","//s[last()]")
And if you are interested go with REPLACE() then use SUBSTITUTE() function to remove last bracket.
=SUBSTITUTE(REPLACE(A1,1,FIND(":",A1,18),""),")","")
And if you are an use of Microsoft 365 insiders or Current Preview channel then can try TEXTAFTER() function.
=TEXTBEFORE(TEXTAFTER(A1,"LOAD:"),")")
THANK YOU, THANK YOU, AND THANK YOU!!!!