Hi team,

So I found this super great function to translate copy in Excel using

=translate_text(copy,"source language","target language")
'Translation Script - use =translate_text(Translation, "From Language Code","To Language Code")
Function translate_text$(text_str$, src_lang$, trgt_lang$)
Dim s1&, s2&, url_str$, rsp$
Const rslt_div$ = "<div class=""result-container"">"
Const url_temp_src$ = "https://translate.google.com/m?hl=[from]&sl=[from]&tl=[to]&ie=UTF-8&prev=_m&q="
url_str = url_temp_src & WorksheetFunction.EncodeURL(text_str)
url_str = Replace(url_str, "[to]", trgt_lang)
url_str = Replace(url_str, "[from]", src_lang)
rsp = WorksheetFunction.WebService(url_str)
s1 = InStr(rsp, rslt_div)
If s1 Then
s1 = s1 + Len(rslt_div)
s2 = InStr(s1, rsp, "</div>")
translate_text = Mid$(rsp, s1, s2 - s1)
End If
End Function
I found all "source" and "target" languages on a google translate page. If I want to translate from, let's say, English to German, the formula would look like:

=translate_text(A1,"en","de")
This works well, however, it only works if "en" and "de" are "hardcoded" in the formula. If I use a variable here it doesn't work, even if those cells contain "en" including the quotation marks. I've also tried to use & to append the quotation marks before and after the referenced cell, but that doesn't seem to work either.
Is there anyone who knows how to tweak this code so I can reference a cell for the "en" and "de" values?

Thanks so much!