And then we can switch this to German, for example. Pretty cool, right? Now, before I show you how you can set these up from scratch, let me introduce you to Excel's new TRANSLATE function. Here, I have some text in German that I want translated into English, and I want to do it in a dynamic way. So, I'm going to go over here, type in "=TRANSLATE," and I see it on this list. I'm going to press Tab. The first thing I need is the text that I want translated,.
Which is sitting in the cell. Then I get to define the source language. So, in which language is this text that I just selected? Well, this is German, so I can scroll down to find the code, which is right here. So, I'm going to click this, and I get "de" in quotations. You can also type these in manually if you know what they are. I want to translate this, let's say, into English. So, in quotations, I'm just going to type in "en," close the bracket, press Enter,.
And my text is translated into English. If I wanted to translate it into Spanish, I'm going to change this to "es," and that's it in Spanish. So, you have lots of choices here. Let's say I want to translate this into Greek, I'm going to select that, press Enter, and that's that in Greek. I have no idea if this is correct or not, so if you can speak Greek, let me know in the comments if this is a correct translation..
Now, here's another cool thing about this function. If I just remove this and I go back here and type in "TRANSLATE," select a text, close the bracket, and Enter, I get my text translated into English. How does it know to translate this into English? Is English the default? No, it's not. It uses my system settings. So, if I don't specify the target language, it picks it up based on my system settings..
If I come over here and type in DETECTLANGUAGE, all I need to provide is the text, and it returns the language code. So, if I press Enter, now I get "en" here and "de" here. Now that we know how to use the TRANSLATE function, let's apply it to some practical use cases. So, here I have a list of instructions that I want translated into different languages depending on whatever the user selects from this dropdown. I don't have a dropdown yet,.
So let's go ahead and insert one. I'll go to Data, Data Validation, let's go with List, and I'll just type in the language codes. So, "en" for English, "de" for German, "es" for Spanish, and "fr" for French, and then press Enter. Okay, so my dropdown is there. Now, I need to connect this with the value that the user selects from this dropdown. This means that we need to replace these with formulas. So, what I'm going to do is Ctrl + C this and paste it somewhere to the side..
Here I'll go here and Ctrl + V. Now we're going to change these to the TRANSLATE function. The text that I want is right here. The source language is "en," and the destination language is whatever the user selects from this dropdown, and I'm going to fix it using the F4 key, close the bracket, press Enter, and now let's just switch this to German. We can see it works. Yeah, it works. Now, if you want to be on the safe side, you can also use the IFERROR function so that in case your.
Result is an error, you just default to whatever is written here. Close the bracket, press Enter, now let's just copy these down. I'm going to highlight this range, press F2, Ctrl + Enter, and we get everything translated into German and Spanish, if we select Spanish from the dropdown. Now, once you have this set up, you can go ahead and hide all of these, or you can group the columns that have your original information. Now, what if you don't want to show the language.
Code in your dropdown, but instead you want to show the full text, so "German" instead of "de"? All you have to do is add one more step to your formula. So, first of all, let's do our dropdown here. I'll go to Data, Data Validation, List. This time, I have my values right here in Excel, so I'm going to reference them and click OK. Now the person can choose between German, English, Spanish, and French. Because I want to connect these ultimately to this value in the dropdown,.
Posts Related:
- Create Best CV in MS Word Best CV Template 2022 DOWNLOAD FREE
- Drive efficiencies in invoice processing for finance departments with Microsoft Power Automate
- Dinamic & Interactive Dashboard no Excel com tabelas pivot e gráficos de pivô Sem macros.
I have to move these out of the way and write formulas here. So, I'm going to copy this whole
Bit, go to the side somewhere, and Ctrl + V this. So now, instead of the text, we're going to use the TRANSLATE function. The text we want translated starts from here. The language of this text is English, so I'm going to put "en" in quotations. I want to translate it into this language, but if I just select this, close the bracket, and press Enter, I get an error. I need to provide the.Language code to the formula. So, what I can do is use VLOOKUP or XLOOKUP to get the code, and I have them right here. So, let's go back to our formula, update this to XLOOKUP. My lookup value is right here, I'm going to fix it using F4. The lookup array is here. Let's fix with F4. What I want returned is right beside it. Let's fix with F4 and close the bracket, press Enter, and now we get our text in German. Now again, you can put this inside the IFERROR function if you want to be.
On the safe side. Otherwise, we can just copy this whole thing down. Let's just select this range, F2, Ctrl + Enter. OK, now this as well, I'm just going to drag this to the side. OK, so now we can easily switch between the different languages. I find this to be a pretty cool function. I remember in my old job, we had to translate some instructions into different languages, and we got them translated, and we, you know,.
Copied and pasted them into another table, and then we used VLOOKUPs to look up these different languages. But the problem was that every time we added a new sentence or we added new instructions, we had to maintain the other languages as well. So, I think it's pretty cool to have everything dynamic, especially for cases like this. Let me know what you think in the comments. I hope you enjoyed this video, and I'll catch you in the next one..
