• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to extract text between two characters > < in excel even having another characters the same, starting and ending the full text..

Kenshin

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

shrivallabha

Excel Ninja
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)
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))
 

bosco_yip

Excel Ninja
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
 
Last edited:

Kelcher

New Member
Hi,

Just another option.



Regards,
Hi,

Just another option.



Regards,
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 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.
 

Kelcher

New Member
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, this is a big formula, hard to build and very useful to manage this kind of situations in Excel.
 

Kelcher

New Member
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))
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.
 

shrivallabha

Excel Ninja
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.
I just color my formula mostly in blue color. That is all. For VBA code, I wrap it in [CODE ] tags.
 
Top