=MID(A3,FIND(">",A3)+1,FIND("</system:String",A3)-FIND(">",A3)-1)
You can probably cut down the formula like below considering the fact that OP's posted data is consistently formatted.Assume your text start from A1, put this on B1 and dragged down:
=LEFT(TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(A1),">",REPT(" ",60)),120),60)),FIND("</",TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(A1),">",REPT(" ",60)),120),60)),1)-1)
Assume your text start from A1, put this on B1 and dragged down:
=LEFT(TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(A1),">",REPT(" ",60)),120),60)),FIND("</",TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(A1),">",REPT(" ",60)),120),60)),1)-1)
Thank you for your help and a good option to find the solution to my question.Hi,
Just another option.
Regards,
Hi,
Just another option.
Regards,
Hi,
Just another option.
Regards,
Thank you for your time and valuable help, this second option you find for my request was perfect. Also thanks for the orientation about how to use it, with XML format.Another option for Excel 2013 and above,
To use Filterxml function by convert the existing tag into a XML format type tag,
=FILTERXML(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,":",""),"-","="),"str","Str"),"systemString")
Regards
Bosco
Thank you for the many times you tried, till finilly find the way to solve my problem. I'm so grateful for your good intentions to help me. Now I have good options to solve this kind of situations in Excel.Hi,
Just another option.
Regards,
Thank you for your help, this is a big formula, hard to build and very useful to manage this kind of situations in Excel.Assume your text start from A1, put this on B1 and dragged down:
=LEFT(TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(A1),">",REPT(" ",60)),120),60)),FIND("</",TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(A1),">",REPT(" ",60)),120),60)),1)-1)
Thank you for be interested in my question, and give me a good solution. Please, could you explain to me better how I can use the codes tags to post my formulas? Thanks in advance.You can probably cut down the formula like below considering the fact that OP's posted data is consistently formatted.
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A3,"<",REPT(" ",99),2),">",REPT(" ",99),1),99,99))
Thanks in advance.
I just color my formula mostly in blue color. That is all. For VBA code, I wrap it in [CODE ] tags.Thank you for be interested in my question, and give me a good solution. Please, could you explain to me better how I can use the codes tags to post my formulas? Thanks in advance.