If you are looking for a way to translate language with formula in VBA then you have come to the right place. Excel doesn’t provide a function for translation. But, you can easily make a user defined function in VBA. The objective of this article is to explain how to use Excel VBA to translate language with a formula.
Step-by-Step Use Excel VBA to Translate Language with Formula
I have taken the following dataset to explain this article. This dataset contains some Text and their source and target Language Codes. I will translate these Texts from the source language to the target language with a formula using Excel VBA. Let’s explore the step-by-step procedures.
Step-01: Create User Defined Function in Excel
Excel does not provide any function for translation. So, I will make a user-defined function in Excel VBA in this step.
- Firstly, go to the Developer tab from Ribbon.
- Secondly, select Visual Basic.
- Thirdly, the Visual Basic Editor window will appear.
- Select the Insert tab.
- Then, select Module.
- After that, a Module will open.
- Next, write the following code in that Module.
VBS 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
- Then, Save the code and go back to your worksheet.
Step-02: Use Formula to Translate Language
In this step, I will use the user defined function that I made in the previous step. I will write a formula to translate the language of the given Text.
- In the beginning, select the cell where you want to get the translated text. Here, I selected Cell E6.
- Then, in Cell E6 write the following formula.
=translate_text(Selecttext, To , From)
=Translate_text(B6,C6,D6);
- After that, press Enter to get the result.
- Finally, you can see that I have copied the formula to the other cells and got the desired output.
#Hari_sir #IT #Tech #Cybersecurity #Programming #Coding #SoftwareDevelopment #DevOps #CloudComputing #NetworkSecurity #DataScience #AI (Artificial Intelligence) #MachineLearning #WebDevelopment #ITSecurity #Blockchain #TechNews #ITJobs #IoT (Internet of Things) #BigData #ITPro #ComputerScience #CodeNewbie #OpenSource #SysAdmin #DigitalTransformation #officeproblems #printingsProblems #Hardware #networking #programming #webDevelopment #OfficeLife #DeskJob #ComputerWork #Productivity #DigitalWorkspace #WorkFlow #TechTuesday #OfficeTech #TaskManagement #TeamCollaboration #RemoteWork #BusinessTech #WorkStation #ProfessionalDevelopment #DigitalSkills #OfficeCulture #Efficiency #WorkflowAutomation #TechSavvy #CareerDevelopment
#excel #exceltips #excelvlookup #exceltutorial #computerwork
0 Comments