• 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..

Kelcher

New Member
I already tried with this formula, but is taking in some cells more text and in others less text that I need.

Thanks a lot, in advance for de valuable help.
 

Attachments

  • This is the formula I have and I need the text in red..JPG
    This is the formula I have and I need the text in red..JPG
    128.4 KB · Views: 15
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)
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))
 
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:
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.
 
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.
 
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.
 
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.
 
Back
Top