On your phone you use your translate but in Google sheets you can actually do it yourself, so let me just wipe out some formulas here and show you how it works. Let’s pretend that we’ve typed in something over here in English. So we’ve got hello, how are you? We want to translate it into another language which is quite easy.
You use a formula that looks kind of like well it looks exactly like this, it’s called Google Translate. Then what it will say is I want to know the text that I want to translate from and what language you want me to put it into. So in my little cell here of A2 I’ve got hello, how are you and then it says OK? What language is that?
So at the moment we know that’s in English so the little code for English is “en” and then we want to convert it to another language. Now over here on the right hand side are all the codes that Google uses for all their languages. So if I want to translate it over to say finnish, I would use an F and I. So I’m going to go back over to my little formula here and type in “fi” and this will now translate for me my hello how are you into Finnish (which is very handy for the Finnish people who will join us today so welcome).
Now that might be good but every time we do it, we have to keep on coming into here and typing in these codes. What would be nicer is if we could come in here and just pick the code that we wanted to and it would just translate it for us. Well we can do that!
Look over here because I’ve got a list of items here and I can do a special trick called data validation. I’m going to use a list from a range (grab all these ones down here) and these are all the languages I can choose. So now I’ve got a bit drop down list, I can come here and choose whatever languageI want it to convert to, look at that, that is fantastic.
So now what I’m going to do is instead of having my “fi” from my one there to work out what it’s going to translate into, I’m just going to grab this little cell in here, so to make this one work I actually used a little formula called vlookup, which we haven’t got time to do, but it’s one of the beautiful ones where it looks at the value goes to the table and grabs the number. So if I type in Basque there. It goes across here till it finds Basque and then grabs that little code there. Cool.
Now, if I come back here, all I need to do is replace this with F2 and what this will do for me now is that will now as I pick a different language, let’s go to Bengali, it’s going to then change it for me live. See how it’s changing it all, this is blowing up live for me and it’s just absolutely fantastic. So that’s really cool.
But you might be going well that’s fantastic as long as I know what this first language is. In our formula here, we’ve told it it’s English. Now Google also has this fantastic formula called ‘detect language’. So believe it or not, in here if I click on detect language in there and then put my bracket around it, it will tell me that it is French, so it’s worked out for me. To make this even better (I mean, you probably all quite excited as well at the moment) what I can do is I can put this formula here, the detect language formula and I can combine it with this formula here so whatever we write in here, Google will work out what it is, and then translate into what we want it to be. How cool is that!
If I come in here now and just copy this little bit here and come in here and then replace this bit here with that cell reference (you’ve got to put a bracket around it because that’s the way it works) go like that, then you will find that this will now read that as English and translate it to whatever language I’d like. Da..da..dah! Absolutely fantastic! So this is a really, really, really, really cool little trick and it works beautifully for you.
It’s using this lovely thing called Google Translate and combining the rest of the two together.
So I really hope you found that exciting and that’s only the tip of the iceberg. There’s much more you can do, but your kids can now go and build a Google translator straight into a spreadsheet, which I think is pretty cool.
[bctt tweet=”Little trick and it works beautifully for you so it’s using this lovely thing, called Google Translate#Google #translate. #utbpln.” username=”adifrancis”]
