Hey there stranger!

Sign up to get access.

World Peace in Google Sheets

About this Tutorial

Combining GoogleTranslate() and DetectLanguage() to figure out what a language is without having to know the two letter codes. And adding a dropdown range to make it easier to find the language you want to translate to. What a great way to solve the frustrating world of International language!

Video Transcript

0:00 You know what? We might not actually solve the world peace in this video, but we're gonna try. One of the things about peace and getting along with other people in other parts of the world is understanding language and understanding what they're saying.
0:15 And we have a really cool tool inside of sheets, which is we can go go to a cell, we can do equals Google Translate.
0:24 But here's the thing, when we use Google Translate, we need three things. We need the text, which is over here in a one, just a little test.
0:34 We need the source language, which I don't know if you know this. Jog lak, I don't know what that is.
0:42 And then we need a target language and we're gonna use E in quotes, English. What if we don't know what this language is?
0:50 There's another formula we compare with this, which is detect language. This is really cool because we don't need to know the two co, the two letter code for another language.
1:03 We don't need to know even what language it is. And if we hit enter, we can translate this to English.
1:08 I like the sheets. Now you might be going to like Google translate and you might be going to detect language and you'll find out what it is.
1:16 This is Swedish apparently. And this Google Translate doesn't have anything <laugh> to do like this. We have detect language, which is really, really cool.
1:28 Now, once we have this detect language and we have this pairing here, we can copy and paste this down a sheet and we can have a bunch of diff I like seats.
1:38 Actually that's supposed to be sheets. It, I have found that it is quite not so exact as you can see here.
1:48 This is all supposed to be, I like spreadsheets. This last one is Greek I think, and it says I love computational sheets.
1:57 Let's dive in and see what this detect language does and we can see maybe how it does it. So now what's really cool is it will tell us the language, but it's a two letter code.
2:10 So I put the two letter codes here in codes. You can also get this sheet if you are a better sheets member, you can get it down below in better sheets.co.
2:20 And these two letter codes will allow you to really debug and detect languages and change it to certain languages. So if add in these codes here, we can see what EL is.
2:34 Let's see, there's Greek. I know why EL is Greek. We'll f*** <laugh>, we'll never know. It's not like we can research these things.
2:40 But yeah, that detect language formula is detecting the language and turning it into a two letter code. And then Google Translate is taking that two letter code and saying, okay, we know what it is.
2:51 Now we're gonna turn it into E N, which is English. If you are wanting to add to this like e N and you want to just write in English, what we could do here is let's say we're gonna put it into here.
3:07 How do we do this? We can do either index match or if we look up right now we have language on the A column and code on the B column.
3:16 We can leave it like this and do V lookup. Let's look at that V lookup. Our search key is going to be the B column I think here.
3:31 Yeah, there we go. V lookup. We're gonna use the B column. B one, we're gonna look in codes, exclamation point A to B.
3:42 We're gonna look at index two. We want to get the code from the second column there. I always use false for, is it sorted?
3:52 We have an A. See, did not find English in V. Look up, let's see English. Ah, it has English American, so we're just going delete that American part and now we found it.
4:07 Cool. So now our V lookup works. If we want to add, if we don't wanna memorize these two letter codes, we want to add 'em here.
4:15 We can copy paste this down. We can also add to this column a dropdown. Dropdown from a range and we'll drop down codes column A, two to A done.
4:31 And now we have a really cool dropdown and now we can translate to any language we want. Let's see, Will it do it for everything?
4:45 Yeah, it's gonna work all the time. And now we can change it tos if we want. Perfect. We can change it to Arabic if we want.
4:54 This is how we can solve world peace in Google Sheets. We now can translate anything to anything knowing, not even knowing what the original language is.
5:05 We have an entire sheet of codes. We have a dropdown that we can select any language we want to get it to.
5:12 This is amazing and I hope this was super useful for you and fun as well. Bye.