Using VBA macro to process then remove html/xml tags

Question

I am looping through cells trying to make certain parts of cell values bold. I have a cell with contents:

<b>This part should be bold</b> but this should not be

I can get the correct part to be bold but the next step is to remove the tags. The following lines cause an issue:

    Cells.Replace What:="<b>", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:="</b>", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

When these lines run (in either order), the whole cell value becomes bold. More specifically, after running it line by line, running either line will have the same result. I am new to VBA and not sure what's causing this.

The function I'm using to make the substring bold is:

fCell.Characters(Start:=m, Length:=n - m + 1).Font.Bold = True

where fCell is being looped over and m and n are the indices locating <b> and </b> respectively.

Tim Williams · Accepted Answer · 2021-09-02 15:57:23Z

3

Once you've set a format for only part of a cell's content, you cannot replace the whole content without losing that partial formatting.

You need to use the Characters method to remove the tags: use Instr() to find the location then set the text there to "":

Sub tester()

    RemoveStrings Range("A1"), Array("<b>", "</b>")

End Sub

Sub RemoveStrings(c As Range, Strings)
    Dim txt, pos As Long
    For Each txt In Strings
        Do
            pos = InStr(1, c.Value, txt, vbTextCompare)
            If pos > 0 Then c.Characters(pos, Len(txt)).Text = ""
        Loop While pos > 0
    Next txt
End Sub

answered Sep 2, 2021 at 15:57

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

benjxg Over a year ago

Out of curiosity, would I be able to find the positions of tags, remove the tags with my current flawed method, then finally do the partial formatting?

Tim Williams Over a year ago

If you remove the tags first, you need to do the work of tracking which parts need to be formatted later and with what format(s).

Collectives™ on Stack Overflow

Using VBA macro to process then remove html/xml tags

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related