Translating A Spreadsheet

[Dazzawm]

Hi, I have a large spreadsheet with a lot of data that is in Spanish. Is there any way or any code etc that would translate the whole spreadsheet to English? Thanks.

 

[mattomrog]

Great! Thanks so much for the quick response. This will be a huge help in translating text in Excel dumps of transactions. I shall be subscribing to your blog. I'll incorporate this and test it and let you know how it goes.

Matt

 

[mattomrog]

Ok, so I tested and it works great! For example:

Input = "BIC - 琴师3月演奏费"
Output = "BIC - March musician playing fee"

This is awesome. I used to think I could code in vba, but I bow down to your skills.

The one caveat is that the "phonetic" option doesn't seem to work. For the same input, I just get a blank output (or for any Chinese input I tested for that matter). The formula I used was =Translate(D9, 12, 1, TRUE), whereas with the default "FALSE", it works fine.

Matt

 

[lasjbp9]

Hey Jon,

Is it possible to reference this site rather than google translate? dict.cc | Wörterbuch Englisch-Deutsch

I've been using your macro and it seems like the results from google translate arent coming back with the best conversions?

Wasnt sure how you grabbed the google translate hyperlink.

Please let me know if this is possible.

Thanks!

 

[Jon von der Heyden]

It probably can be done, but the routine would be very very different. So a rewrite essentially, not an amendment.

 

[Crazy_eng]

Thank You very much Jon and Cody

This was the perfect piece of code just at the right time judging by the posting dates.

It has been translating beautifully except for one sentce which I was trying to translate (I am posting in the hope this may help out othe users, if they have a similar problem)

The sentence that gave me the problem was this
"I here by declare that I have completed all work as detailed above in accordance with any relevant Technical Manual and/or device documentation"

Translating it into French the translate function returned
"Je déclare ici par ce que je viens de terminer tous les travaux comme indiqué ci-dessus"
Which is only about half of the string.

THE PROBLEM was the the full French translation had a "," in it.
"Je déclare ici par ce que je viens de terminer tous les travaux comme indiqué ci-dessus , conformément à toute Manuel technique pertinente et / ou la documentation de l'appareil"

This meant the function for stripping out the desired part of the string was spliting it at the "," and I only got the translation up to that point
For lngItem = 3 To UBound(varSplitText)
strTransText = strTransText & Split(varSplitText(lngItem), ",")(0)
Next

THE SOLUTION
The returned translated string looks like this "[Translated Text]","[Original Text]"
so change the delimiter to a ", and it will be able to identify the end of the translated string insted of the the first comma

This is what I did
Dim delimitChar as string
delimitChar = Chr(34) & "," 'Chr(34) is the character for "

change the problem line to
For lngItem = 3 To UBound(varSplitText)
strTransText = strTransText & Split(varSplitText(lngItem), delimitChar)(2)
Next

I am just a novice, but this solved the problem for me.
Maybe there is a nicer way to do this?

Could you tell me if this might lead to other problems, with other translations?

Thanks again guys, top piece of code

 

[Crazy_eng]

I just found out I am getting the whole of other translations I was missing out on too (though I thought the reasons were different)
This fix works for anything with a comma in it.
like this line which I had returned without the list of numbers due to the comma separating them

"Performance and calibration checks; sections 8.1.2, 8.1.3, 8.1.4, (8.2.1, 8.2.2) of the Technical Manual" Translated to
"Contrôles de performance et d'étalonnage , les sections 8.1.2 , 8.1.3 , 8.1.4 , ( 8.2.1 , 8.2.2 ) du Manuel technique"

Instead of stripping all the numbers out!

 

[raj249]

Hi All,

I am new to excel.Did Just copy paste stuff before.

I downloaded the file ,but It gives me error. Update code to use on 64 bit system.

Can anyone modify code to use on 64 bit.system.

Bye the way I am using win8 64bit and office 2013 64bit.


Thanks all

 

[raj249]

Any Help?

 

[Jon von der Heyden]

Any Help?

Nope. Not until I have the time to make the update. I'm snowed under.

 

[raj249]

Nope. Not until I have the time to make the update. I'm snowed under.

Ok. I will wait.

Thanks for reply