Replace function, how to replace not all founded strings?

Question

I create XML file from excel file. I save a string from one cell in a variable sObserved. When in that string I have a character "&" it should be replaced to "&"and when I have a character ";" it should be replaced to ";". I use for this function Replace, this is my code:

        sObserved = Replace(sObserved, "&", "&amp;")
        sObserved = Replace(sObserved, ";", "&#59;")

But this can't work good, because when it will replace "&" on "&", the ";" will appear and next operation will change it to "&amp;" If I'll change an order it also will be wrong, because then sign "&" in "&#59" will be replaced.

Is there any possibility to replace it just like I wanted? I will be gratefull for any ideas because I stuck here.

Maybe first use pipe character (| = AltGr+W) for replacing (e.g. &amp| and &#59|) and then after all replaces, replace | character with ;. This won't work however, if pipe character is anywhere used as a content. — Jure
– Jure, Commented Dec 16, 2015 at 9:39
Possible duplicate of How to HTML encode or transliterate "high" characters in Excel? — Madivad
– Madivad, Commented Dec 16, 2015 at 9:45

Leo Chapiro · Accepted Answer · 2015-12-16 10:03:04Z

2

Try this:

sObserved = Replace(sObserved, "&", "&amp;")
sObserved = Replace(sObserved, ";", "&#59;")
sObserved = Replace(sObserved, "&amp&#59;", "&amp;")

edited Dec 16, 2015 at 10:03

answered Dec 16, 2015 at 9:41

Leo Chapiro

14k8 gold badges67 silver badges95 bronze badges

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

2 Comments

GohanP Over a year ago

Probably in last line you mean `(sObserved, "&amp;", "&"). Thanks! There is so easy sollution when I combined too much earlier :)

Madivad Over a year ago

I like simple and elegant solutions, one additional Replace, perfect :)

Fred · Accepted Answer · 2015-12-16 09:42:29Z

1

This function is from http://www.codeproject.com/Articles/33064/VBScript-HTML-Encode. You may need to tweek it but it does a character by character check.

Function HTMLEncode(ByVal sVal)

    sReturn = ""

    If ((TypeName(sVal)="String") And (Not IsNull(sVal)) And (sVal<>"")) Then

        For i = 1 To Len(sVal)

            ch = Mid(sVal, i, 1)

            Set oRE = New RegExp : oRE.Pattern = "[ a-zA-Z0-9]"

            If (Not oRE.Test(ch)) Then
                ch = "&#" & Asc(ch) & ";"
            End If

            sReturn = sReturn & ch

            Set oRE = Nothing
        Next
    End If

    HTMLEncode = sReturn
End Function

answered Dec 16, 2015 at 9:42

Fred

5,8684 gold badges47 silver badges76 bronze badges

Comments

Madivad · Accepted Answer · 2015-12-16 09:47:59Z

What you're after is a function to encode the output to be HTML friendly: htmlEncode. There are several scripts/functions around the web people have wrote, here is one:

' Encode an string so that it can be displayed correctly
' inside the browser.
'
' Same effect as the Server.HTMLEncode method in ASP

Function HTMLEncode(ByVal Text As String) As String
Dim i As Integer
Dim acode As Integer
Dim repl As String

HTMLEncode = Text

For i = Len(HTMLEncode) To 1 Step -1
    acode = Asc(Mid$(HTMLEncode, i, 1))
    Select Case acode
        Case 32
            repl = "&nbsp;"
        Case 34
            repl = "&quot;"
        Case 38
            repl = "&amp;"
        Case 60
            repl = "&lt;"
        Case 62
            repl = "&gt;"
        Case 32 To 127
            ' don't touch alphanumeric chars
        Case Else
            repl = "&#" & CStr(acode) & ";"
    End Select
    If Len(repl) Then
        HTMLEncode = Left$(HTMLEncode, i - 1) & repl & Mid$(HTMLEncode, _
            i + 1)
        repl = ""
    End If
Next
End Function

ref: http://www.devx.com/vb2themax/Tip/19162

There is another here: http://www.codeproject.com/Articles/33064/VBScript-HTML-Encode but that seems to encode everything that's not a character or letter. The regex in this one could probably be expanded a little better to include those that are ok for HTML.

Function HTMLEncode(ByVal sVal)

sReturn = ""

If ((TypeName(sVal)="String") And (Not IsNull(sVal)) And (sVal<>"")) Then

    For i = 1 To Len(sVal)

        ch = Mid(sVal, i, 1)

        Set oRE = New RegExp : oRE.Pattern = "[ a-zA-Z0-9]"

        If (Not oRE.Test(ch)) Then
            ch = "&#" & Asc(ch) & ";"
        End If

        sReturn = sReturn & ch

        Set oRE = Nothing
    Next
End If

HTMLEncode = sReturn
End Function

Collectives™ on Stack Overflow

Replace function, how to replace not all founded strings?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related