Google translater User function returned symbol code

[Dangermike101]

Hello all,

So i am using a google translater user function in Excel VBA,
It seems to work well however the main issue i am having is that for French it returns "&#39" instead of an apostrophe.

Below is the coding.


Function Translate$(sText$, FromLang$, ToLang$)
Dim p1&, p2&, url$, resp$
Const DIV_RESULT$ = "<div class=""result-container"">"
Const URL_TEMPLATE$ = "Google Translate"
url = URL_TEMPLATE & WorksheetFunction.EncodeURL(sText)
url = Replace(url, "[to]", ToLang)
url = Replace(url, "[from]", FromLang)
resp = WorksheetFunction.WebService(url)
p1 = InStr(resp, DIV_RESULT)
If p1 Then
p1 = p1 + Len(DIV_RESULT)
p2 = InStr(p1, resp, "</div>")
Translate = Mid$(resp, p1, p2 - p1)
End If
End Function


Also for some reason it only translates if i put the [To] language code in the [From] sections like so
=Translate('RMA Information'!A17, "FR", "")



Any help would be greatly appreciated