translatable text in formulas Excel

Question

Hello! Could anyone help me with this?

How can I translate formulas in Excel? My file contains things like [[="2. JAARREKENING "&regelblad!F10]] and [[="Hierbij brengt het bestuur rapport uit over de jaarrekening "®elblad!F10&" van "®elblad!F2]]

How can I persuade Studio to make the tekst in between <"> and < "&>?

I cannot write regex yet and I suspect I will need that for this particular project.

Kind regards,

Rens

Raphaël Toussaint · Answer

Hello Rens, 
 I have had a look at your use case and the relevant Studio file type settings, and unfortunately, I have come to the conclusion that it is currently not possible to include Excel formulas for translation in Trados Studio. 
 The only potential solution I could imagine would be to "unformulate" all concerned formulas by preceding them with an apostrophe so they are considered as literal text. I don't know if this can be done in a batch search & replace operation: 
 Then define the parts of the formula which don't need to be translated as internal tags in the concerned file type settings. I used the following regular expressions to achieve that, but these might need some tweaking to exactly fit your needs: 
 
 ^[^"]+?" (from the beginning of the formula until the first quote (included) 
 "&[^"]+"? (from a quote followed by an ampersand [&]character all characters which aren't a quote (and including the next quote if any))

This leads to the following outcome in Trados Studio which I think should be close to what you want: 
 
 Alternatively, the newly created tags could also be defined as "Exclude" in the advanced settings for each tag which would have the following result in Trados Studio: 
 After translation, you just need to remove the apostrophe to turn the translated text into formulas again. 
 I hope this helps. If anything is unclear or if you run into trouble adapting this to your exact case, don't hesitate to et back to me! 
 Kind regards and a great end of the week, Rapha&euml;l

Trados Studio > 5. Regex and XPath

translatable text in formulas Excel