Question Replace characters like <div>, <br>

[neideb]

I have placed this question in another post but maybe it was in the wrong place.
I imported some fields from another database and my memo field displays some caracters like <div>_____</div>. Also there are some strings ending by <br> and others.
I know that chr(13) and chr(10) means carriage return and line feed but do not know how to work with <div> and others.
my query is:
ChangeStr([MyField];Car$(11);Car$(13) & Car$(10);1)
Could you pls help me on this?
Tks a lot...
NeideB

 

[sparks80]

Hi,

What is your aim, do you want to remove the HTML tags (like <div>) from the text or replace them with something else?

 

[spikepl]

The propery Text Format of your memo field in the table is probably set to Plain Text instead of RichText, or the same for the textbox control in which you are viewing the memo field.

 

[neideb]

Hi Sparks80 and Splkepl, tks for yr attention. I want to delete <div> that is at the begining of sentence and replace </div> or <br> at the end of sentence for next line and return character.
Splkepl, The field is set to rich text. I have already copied the records to a new field set as plain text but the are still showing.
Tks again.

 

[sparks80]

Hi,

Try pasting this code into a VBA module. The code is untested.

In the VBA Editor you will need to go to Tools > References and check the box next to "Microsoft VBScript Regular Expressions 5.5".

You can then use this function in your query like this:

CorrectedString: ReplaceUnwantedTags([MyField])

Public Function ReplaceUnwantedTags(strInput As String) As String
    Dim re As New RegExp
    
    re.Pattern = "<div>"
    re.IgnoreCase = True
    ' This will match all instances of the pattern
    re.Global = True

    ' Replace <Div> with zero-length string
    If reDiv.Test(strInput) Then
        strInput = reDiv.Replace(strInput, vbNullString)
    End If

    re.Pattern = "<(/div|br)>"
    re.IgnoreCase = True
    ' This will match all instances of the pattern
    re.Global = True

    ' Replace </Div> and <BR> with Carriage return and line feed
    If reDiv.Test(strInput) Then
        strInput = reDiv.Replace(strInput, vbCrLf)
    End If

    ReplaceUnwantedTags = strInput

    Set re = Nothing
End Function

 

[neideb]

Tks so much Sparks80. I will backup my db and test your function.
I will let you know soon.
Have a nice day. Tks

 

[sparks80]

Hi,

OK, having seen some sample data I have been able to write and test the function for you. I have made some changes as follows:
1) The data often contains HTML entities for the < and > symbols ( < and > ). Therefore I have used regular expressions to replace the entities with the correct symbol
2) To tidy up the code I have used two arrays to store the regular expression patterns, and replacement strings
3) Included an option to remove extra white space from the end of the string
4) Included an option to remove blank lines from the string

IF you wanted to remove additional white space, but keep the blank lines the SQL for your query would become:

UPDATE Tb1 SET Tb1.Fd1 = ReplaceUnwantedTags([Fd1],True,False);

And here is the the revised function

Public Function ReplaceUnwantedTags(strInput As String, _
    Optional blnTrimWhiteSpace As Boolean = False, _
    Optional blnRemoveBlankLines As Boolean = False) As String
    Dim re As New RegExp
    Dim strPatternArray As Variant
    Dim I As Integer
    
    ' Needs to be in this order - "less than" and "greater than"
    ' html entities need to be replaced before the tags can be matched
    ' < becomes <
    ' > becomes >
    ' <div> becomes zero length string
    ' </div> and <br> become line feed and carriage return
    
    strPatternArray = Array("<", ">", "<div>", "<(/div|br)>")
    strReplaceArray = Array("<", ">", vbNullString, vbCrLf)
    
    For I = 0 To UBound(strPatternArray)
        re.Pattern = strPatternArray(I)
        re.IgnoreCase = True
        re.Global = True
        
        If re.Test(strInput) Then
            strInput = re.Replace(strInput, strReplaceArray(I))
        End If
    Next
    
    If blnRemoveBlankLines = True Then
        re.Pattern = "^\s*$"
        re.Global = True
        re.Multiline = True
        If re.Test(strInput) Then
            strInput = re.Replace(strInput, vbNullString)
        End If
    End If
    
    If blnTrimWhiteSpace Then
        ReplaceUnwantedTags = Trim(strInput)
    Else
        ReplaceUnwantedTags = strInput
    End If
    
    Set re = Nothing
End Function

 

[neideb]

Hi all,

I want to thank very much all help Sparks80 gave to me writing the code to replace those characters that appeared in my database after converting fields to rich text.
It is really a very good code and hope it can help other people in need.
Tks Sparks80

 

[neideb]

Hi Sparks80,

ALso this function is very good. To make it work better for me I added to it the following sentence:
Option Explicit
Dim strReplaceArray as Variant
Set the table to Plain Text

Also it it very important not forget to do the folowing:

In the VBA Editor you will need to go to Tools > References and check the box next to "Microsoft VBScript Regular Expressions 5.5".
Tks very much.
Neide