View original (French)

Remove a string of characters enclosed between two symbols

Solved
Greg le novice -  
 Claire -
Bonjour,

I would like, as my title indicates, to remove character strings contained between two symbols: "<" and ">".
----------------------
Example:

<boat> it's nice weather today </b the little rabbit> </h> <toto>

It should remain in my cell:
it's nice weather today
---------------------
Do we have to go through VBA?
If yes, I'm stuck on how to indicate in my code that it needs to remove the symbols ("<", ">") and how to include in the removal the characters between these two symbols.
And especially to make the removal repeat as the symbols appear multiple times in a single cell.

Thank you in advance for your help.

Sincerely,

Greg the novice

2 answers

  1.
    Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    =Hello
    without VBA, it must be assumed that your data always has the following configurations
    either a space in front of < or a space behind > regardless of the number of words to be removed before or after the text
    We can then execute in two stages:

    first stage, enter the field (for example A:A) and replace (press ctrl and h key
    Replace: place > < (with a space)
    by: place: >< (without space)

    then in B the formula:

    =MID(A1;FIND("> ";A1;1)+2;FIND("<";A1;1)-FIND("> ";A1;1)-2)

    Note > and followed by a space in the quotes
    < and preceded by a........

    to be tested in all your cases

    regards

    and to complete in case of empty cell in A and if the text to be output starts at the beginning of the sentence:
    always after the "replace" treatment

    =IF(A1="";"";IF(LEFT(A1;1)<>"<";MID(A1;1;FIND("<";A1;1));MID(A1;FIND("<";A1;1);FIND("> ";A1;1)-FIND("<";A1;1))))

    and to finish, if you want to avoid the replace manipulation, you can use:

    =IF(LEFT(A1;1)<>"<";MID(SUBSTITUTE(A1;"> <";"><");1;FIND("<";SUBSTITUTE(SUBSTITUTE(A1;"> <";"><");"> <";"><");1));MID(SUBSTITUTE(A1;"> <";"><");FIND("> ";SUBSTITUTE(A1;"> <";"><");1)+2;FIND("<";SUBSTITUTE(A1;"> <";"><");1)-FIND("> ";SUBSTITUTE(A1;"> <";"><");1)-2))

    regards

    To err is human, to persist is diabolical
    0
    1. Greg le novice
       
      Hello Vaucluse,

      Thank you for responding so quickly to my issue.
      I'm glad there has been progress on my problem.
      However, after replacing all the "> <" with "><" and applying the formula on a cell, only the first string of characters is extracted and not the rest.
      Example:
      <boat>dolphin < truck ></b> toto
      I only get: dolphin
      Instead of getting: dolphin toto

      Thank you in advance,

      Greg the novice

      P.S.: I don't understand how the formula works by the way..
      0
    2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      It wasn't mentioned in the initial question??

      What we need before looking further is to know all of your scenarios. How many separate words are there to extract like this?

      In the meantime, take a look at the additions to my previous message
      .. and to go further, I think we will need to go through VBA to remove the words in brackets by <> but that's not my area.
      0
    3. Greg le novice Posted messages 11 Status Member
       
      The formulas above work perfectly and indeed respond to the first request I made.
      A big thank you, this helps me a lot.
      ----
      Yes, in my example I should have put "toto" outside the brackets so that we get: dolphin toto
      AND I proposed these examples to make my request clearer.
      In my case, this corresponds to whole paragraphs in an Excel cell, where there are mixed in, for example, "</b> or </g>. There are many of them.
      ----
      So I suppose I need to go through VBA. That doesn't bother me. But I still feel stuck when it comes to expressing these formulas in VBA.
      0
    4. Greg le novice Posted messages 11 Status Member
       
      An example of a cell to be as clear as possible:

      <div style="border: 1px solid #C0C0C0; text-align: justify; font-family: Calibri; padding: 10 10 15 10px; font-size: 12px; background-color: #FFE; width: 500px;">
      <h1 style="text-align: center; color: #069; font-size: 16px;"> Access </h1>
      <br/>

      This offer provides the Microsoft ACCESS software, a DBMS for office applications.
      </p>
      <div style="color: #069">
      <strong>Please indicate in the comment field next to: </strong>
      <ul style="list-style-type: square; margin-top: 5px;">
      <li>the justification of the need</li>
      <li>the references of the position concerned by the installation</li>
      </ul>
      </div>
      </div>
      0
  2. Morgothal Posted messages 1350 Status Member 184
     
    Hello,
    I made this macro, it works with all the examples you posted above (it's nice weather, the dolphin, and the DBMS).
    This macro needs to be executed multiple times until it detects that there is nothing left to delete: 

    Sub deletion() Dim string, string2 As String string = Range("A1") If InStr(string, "<") = 0 Then MsgBox ("Nothing left to delete!") Exit Sub End If If Left(string, 1) = "<" Then string = Right(string, Len(string) - InStr(string, ">")) End If If InStr(string, "<") <> 0 Then string2 = Range("A2") string2 = string2 & Left(string, InStr(string, "<") - 1) string = Right(string, Len(string) - InStr(string, "<") + 1) Range("A1") = string Range("A2") = string2 Else string2 = Range("A2") string2 = string2 & string Range("A1") = string Range("A2") = string2 End If End Sub

    See you later!

    --
    -------------------
    Best regards,
    Clément
    0
    1. Morgothal Posted messages 1350 Status Member 184
       
      Small clarification, in my tests, the string to be processed was in A1, and it wrote in A2 this string without the <texts_a_supprimer>.
      0
    2. Greg le novice Posted messages 11 Status Member
       
      Hello,

      Thank you Morgothal, your macro works perfectly. A few details to modify but otherwise a big, big thank you!

      Best regards,

      Greg the novice
      0
    3. Claire
       
      Hello,

      Thank you for your macro. Could you please tell me the changes to make it apply to all the cells in my column N and have the new values displayed in column M for example?

      Thank you in advance.
      0