Can Excel translate languages?
Part 1:
There is a built-in Translation tool on the Review tab in Excel. It shows the answers in a task pane, but it does a bad job of getting those answers back to the Excel grid.
Five new books were published this summer. Check them out at: Products
Part 2: You can use a small bit of Power Query code from Smozgur at MrExcel to solve the problem. Get that code from: Using Power Query in Excel to Translate Data Using the Google Translation API
Table of Contents
(0:00) Can Excel translate languages
(0:24) Translation on Review tab
(1:15) Paste back to Excel fails
(1:47) Five new books published
(3:00) Translating with Power Query
(5:12) Wrap-up
Part 1:
There is a built-in Translation tool on the Review tab in Excel. It shows the answers in a task pane, but it does a bad job of getting those answers back to the Excel grid.
Five new books were published this summer. Check them out at: Products
Part 2: You can use a small bit of Power Query code from Smozgur at MrExcel to solve the problem. Get that code from: Using Power Query in Excel to Translate Data Using the Google Translation API
Table of Contents
(0:00) Can Excel translate languages
(0:24) Translation on Review tab
(1:15) Paste back to Excel fails
(1:47) Five new books published
(3:00) Translating with Power Query
(5:12) Wrap-up
Transcript of the video:
Can Excel translate languages?
Well, this is great.
I went back to this video from 12 years ago and watched what I then described as a brand new oddity in Excel 2007, one cell at a time, only 20 languages. I hoped it would get better.
It's sort of gotten better.
So here's a bunch of phrases that I'd like to translate.
We come to the Review tab, have all those phrases selected, and you open the Translate panel over here.
And it detects that all of those phrases are in English, and then I get to choose what language I'd like to go to. So we'll just choose a language here.
Lot more than 20.
I'll choose Czech. All right, now here's the problem.
Wouldn't it be nice if we could get these values back into, let's say, column C, right next to the original data?
When I right click, there's an option to Select All, but it doesn't select all. Super annoying.
It's a weird box.
As I hover over things here, it is showing me a translation down at the bottom.
Right?
So you can see word by word, but that's not what I want to do.
So if you are very careful, you can manage to select everything and then Control C, that's beautiful, but then come back here and Control V to paste. Boy, it takes forever.
It must be going back out to the API over and over and over again.
And then, crushing, it puts all of the phrases in one cell instead of going to the next line.
Now I have something better than this. Not as easy as this, but not that hard at all.
We just had our Labor Day holiday yesterday here in the United States, which is the traditional end of summer. As I look back, what a summer it was.
Five new books came out.
All of these are now available in print, including MrExcel 2022 and Liam Bastick's Financial Modeling in Power BI.
These both came out September first.
Wyn Hopkins' book on power BI won't be shipping to the rest of the world until November first, but I have it now, and then Oz and I wrote Guerrilla Data Analysis Using Microsoft Excel, the third edition.
Now you'll notice here that I have this in two different categories, power BI books and Excel books, and straddling that is Mike Girvin's huge 800 page book with over a hundred pages on power BI, a huge section on power query, and 600 pages on Excel.
Right? So Mike's book belongs in both categories.
Click that eye in the top right hand corner to get to a page where you can order any of these.
So back to today's question, can Excel translate languages?
There's this great post by Smozgur at the MrExcel message board.
I'll put the link down in the YouTube description, or in that eye in the top right hand and corner.
So this article talks about using power query to translate data using the Google translation API, and it's remarkably easy to use.
You just need to scroll down to where it says, "Copy and paste the following code into a blank query". I'm going to copy that data right there.
Control C. Now a little bit of pre-work here.
I'm going to add a heading above this of English, because this data's coming from English.
Make that bold, and then Control T to make it into a table.
And that table's going to be called Table 1, but I need to rename that to be Original.
The code is expecting a table called Original with a field called English.
On the data tab, over here on the far left hand side, Get Data, From Other Sources, all the way down at the bottom, Blank Query.
You're taking it over to the power query editor. For a lot of you, this will be brand new.
Go to the View tab, go to the advanced editor, where they give you a tiny four line start of the code.
We're going to delete all that, so select it and then paste, Control V. Now, couple of things here.
The source language is currently EN, that's English, and then the To language is currently TR, that's Turkish. Let's try ES for Spanish.
And then down here, the new column, let's call it Turkish, let's call it Spanish, and you'll have to adjust those as appropriate for your data set.
And again, if you don't have a heading called English, this is not going to work.
Press Done, and there are my English phrases, and next to it the Spanish phrases.
Home.
Close and Load, Close and Load To an existing worksheet. And we'll just put it right here in Column D.
And there you go.
So, wholesale translation using the Google API, a bunch of English phrases to a bunch of Spanish phrases.
It'd be nice if this review translate was easier to use.
It seems like just a couple of tweaks there, that being able to copy all of the data at once and paste it back into multiple cells would be very useful.
But thankfully Smozgur at the MrExcel message board solves the problem with this great bit of power query. Well hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, this is great.
I went back to this video from 12 years ago and watched what I then described as a brand new oddity in Excel 2007, one cell at a time, only 20 languages. I hoped it would get better.
It's sort of gotten better.
So here's a bunch of phrases that I'd like to translate.
We come to the Review tab, have all those phrases selected, and you open the Translate panel over here.
And it detects that all of those phrases are in English, and then I get to choose what language I'd like to go to. So we'll just choose a language here.
Lot more than 20.
I'll choose Czech. All right, now here's the problem.
Wouldn't it be nice if we could get these values back into, let's say, column C, right next to the original data?
When I right click, there's an option to Select All, but it doesn't select all. Super annoying.
It's a weird box.
As I hover over things here, it is showing me a translation down at the bottom.
Right?
So you can see word by word, but that's not what I want to do.
So if you are very careful, you can manage to select everything and then Control C, that's beautiful, but then come back here and Control V to paste. Boy, it takes forever.
It must be going back out to the API over and over and over again.
And then, crushing, it puts all of the phrases in one cell instead of going to the next line.
Now I have something better than this. Not as easy as this, but not that hard at all.
We just had our Labor Day holiday yesterday here in the United States, which is the traditional end of summer. As I look back, what a summer it was.
Five new books came out.
All of these are now available in print, including MrExcel 2022 and Liam Bastick's Financial Modeling in Power BI.
These both came out September first.
Wyn Hopkins' book on power BI won't be shipping to the rest of the world until November first, but I have it now, and then Oz and I wrote Guerrilla Data Analysis Using Microsoft Excel, the third edition.
Now you'll notice here that I have this in two different categories, power BI books and Excel books, and straddling that is Mike Girvin's huge 800 page book with over a hundred pages on power BI, a huge section on power query, and 600 pages on Excel.
Right? So Mike's book belongs in both categories.
Click that eye in the top right hand corner to get to a page where you can order any of these.
So back to today's question, can Excel translate languages?
There's this great post by Smozgur at the MrExcel message board.
I'll put the link down in the YouTube description, or in that eye in the top right hand and corner.
So this article talks about using power query to translate data using the Google translation API, and it's remarkably easy to use.
You just need to scroll down to where it says, "Copy and paste the following code into a blank query". I'm going to copy that data right there.
Control C. Now a little bit of pre-work here.
I'm going to add a heading above this of English, because this data's coming from English.
Make that bold, and then Control T to make it into a table.
And that table's going to be called Table 1, but I need to rename that to be Original.
The code is expecting a table called Original with a field called English.
On the data tab, over here on the far left hand side, Get Data, From Other Sources, all the way down at the bottom, Blank Query.
You're taking it over to the power query editor. For a lot of you, this will be brand new.
Go to the View tab, go to the advanced editor, where they give you a tiny four line start of the code.
We're going to delete all that, so select it and then paste, Control V. Now, couple of things here.
The source language is currently EN, that's English, and then the To language is currently TR, that's Turkish. Let's try ES for Spanish.
And then down here, the new column, let's call it Turkish, let's call it Spanish, and you'll have to adjust those as appropriate for your data set.
And again, if you don't have a heading called English, this is not going to work.
Press Done, and there are my English phrases, and next to it the Spanish phrases.
Home.
Close and Load, Close and Load To an existing worksheet. And we'll just put it right here in Column D.
And there you go.
So, wholesale translation using the Google API, a bunch of English phrases to a bunch of Spanish phrases.
It'd be nice if this review translate was easier to use.
It seems like just a couple of tweaks there, that being able to copy all of the data at once and paste it back into multiple cells would be very useful.
But thankfully Smozgur at the MrExcel message board solves the problem with this great bit of power query. Well hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
