{"id":586,"date":"2016-12-13T18:04:41","date_gmt":"2016-12-13T17:04:41","guid":{"rendered":"https:\/\/blog.defrent.de\/?p=586"},"modified":"2024-04-05T16:14:55","modified_gmt":"2024-04-05T14:14:55","slug":"preparing-ms-excel-spreadsheets-for-translation-in-your-cat-tool","status":"publish","type":"post","link":"https:\/\/www.defrent.de\/en\/2016\/12\/preparing-ms-excel-spreadsheets-for-translation-in-your-cat-tool\/","title":{"rendered":"Preparing MS Excel Spreadsheets for Translation in Your CAT Tool"},"content":{"rendered":"<div class=\"shariff shariff-align-flex-end shariff-widget-align-flex-start\" style=\"display:none\"><ul class=\"shariff-buttons theme-round orientation-horizontal buttonsize-small\"><li class=\"shariff-button mastodon\" style=\"background-color:#ccc\"><a href=\"https:\/\/s2f.kytta.dev\/?text=Preparing%20MS%20Excel%20Spreadsheets%20for%20Translation%20in%20Your%20CAT%20Tool https%3A%2F%2Fwww.defrent.de%2Fen%2F2016%2F12%2Fpreparing-ms-excel-spreadsheets-for-translation-in-your-cat-tool%2F via @defrentck@hessen.social\" title=\"Share on Mastodon\" aria-label=\"Share on Mastodon\" role=\"button\" rel=\"noopener nofollow\" class=\"shariff-link\" style=\"; background-color:#79b428; color:#fff\" target=\"_blank\"><span class=\"shariff-icon\" style=\"\"><svg width=\"75\" height=\"79\" viewBox=\"0 0 75 79\" fill=\"none\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\"><path d=\"M37.813-.025C32.462-.058 27.114.13 21.79.598c-8.544.621-17.214 5.58-20.203 13.931C-1.12 23.318.408 32.622.465 41.65c.375 7.316.943 14.78 3.392 21.73 4.365 9.465 14.781 14.537 24.782 15.385 7.64.698 15.761-.213 22.517-4.026a54.1 54.1 0 0 0 .01-6.232c-6.855 1.316-14.101 2.609-21.049 1.074-3.883-.88-6.876-4.237-7.25-8.215-1.53-3.988 3.78-.43 5.584-.883 9.048 1.224 18.282.776 27.303-.462 7.044-.837 14.26-4.788 16.65-11.833 2.263-6.135 1.215-12.79 1.698-19.177.06-3.84.09-7.692-.262-11.52C72.596 7.844 63.223.981 53.834.684a219.453 219.453 0 0 0-16.022-.71zm11.294 12.882c5.5-.067 10.801 4.143 11.67 9.653.338 1.48.471 3 .471 4.515v21.088h-8.357c-.07-7.588.153-15.182-.131-22.765-.587-4.368-7.04-5.747-9.672-2.397-2.422 3.04-1.47 7.155-1.67 10.735v6.392h-8.307c-.146-4.996.359-10.045-.404-15.002-1.108-4.218-7.809-5.565-10.094-1.666-1.685 3.046-.712 6.634-.976 9.936v14.767h-8.354c.109-8.165-.238-16.344.215-24.5.674-5.346 5.095-10.389 10.676-10.627 4.902-.739 10.103 2.038 12.053 6.631.375 1.435 1.76 1.932 1.994.084 1.844-3.704 5.501-6.739 9.785-6.771.367-.044.735-.068 1.101-.073z\"\/><defs><linearGradient id=\"paint0_linear_549_34\" x1=\"37.0692\" y1=\"0\" x2=\"37.0692\" y2=\"79\" gradientUnits=\"userSpaceOnUse\"><stop stop-color=\"#6364FF\"\/><stop offset=\"1\" stop-color=\"#563ACC\"\/><\/linearGradient><\/defs><\/svg><\/span><\/a><\/li><li class=\"shariff-button linkedin\" style=\"background-color:#ccc\"><a href=\"https:\/\/www.linkedin.com\/sharing\/share-offsite\/?url=https%3A%2F%2Fwww.defrent.de%2Fen%2F2016%2F12%2Fpreparing-ms-excel-spreadsheets-for-translation-in-your-cat-tool%2F\" title=\"Share on LinkedIn\" aria-label=\"Share on LinkedIn\" role=\"button\" rel=\"noopener nofollow\" class=\"shariff-link\" style=\"; background-color:#79b428; color:#fff\" target=\"_blank\"><span class=\"shariff-icon\" style=\"\"><svg width=\"32px\" height=\"20px\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 27 32\"><path fill=\"#0077b5\" d=\"M6.2 11.2v17.7h-5.9v-17.7h5.9zM6.6 5.7q0 1.3-0.9 2.2t-2.4 0.9h0q-1.5 0-2.4-0.9t-0.9-2.2 0.9-2.2 2.4-0.9 2.4 0.9 0.9 2.2zM27.4 18.7v10.1h-5.9v-9.5q0-1.9-0.7-2.9t-2.3-1.1q-1.1 0-1.9 0.6t-1.2 1.5q-0.2 0.5-0.2 1.4v9.9h-5.9q0-7.1 0-11.6t0-5.3l0-0.9h5.9v2.6h0q0.4-0.6 0.7-1t1-0.9 1.6-0.8 2-0.3q3 0 4.9 2t1.9 6z\"\/><\/svg><\/span><\/a><\/li><li class=\"shariff-button xing\" style=\"background-color:#ccc\"><a href=\"https:\/\/www.xing.com\/spi\/shares\/new?url=https%3A%2F%2Fwww.defrent.de%2Fen%2F2016%2F12%2Fpreparing-ms-excel-spreadsheets-for-translation-in-your-cat-tool%2F\" title=\"Share on XING\" aria-label=\"Share on XING\" role=\"button\" rel=\"noopener nofollow\" class=\"shariff-link\" style=\"; background-color:#79b428; color:#fff\" target=\"_blank\"><span class=\"shariff-icon\" style=\"\"><svg width=\"32px\" height=\"20px\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 25 32\"><path fill=\"#126567\" d=\"M10.7 11.9q-0.2 0.3-4.6 8.2-0.5 0.8-1.2 0.8h-4.3q-0.4 0-0.5-0.3t0-0.6l4.5-8q0 0 0 0l-2.9-5q-0.2-0.4 0-0.7 0.2-0.3 0.5-0.3h4.3q0.7 0 1.2 0.8zM25.1 0.4q0.2 0.3 0 0.7l-9.4 16.7 6 11q0.2 0.4 0 0.6-0.2 0.3-0.6 0.3h-4.3q-0.7 0-1.2-0.8l-6-11.1q0.3-0.6 9.5-16.8 0.4-0.8 1.2-0.8h4.3q0.4 0 0.5 0.3z\"\/><\/svg><\/span><\/a><\/li><li class=\"shariff-button facebook\" style=\"background-color:#ccc\"><a href=\"https:\/\/www.facebook.com\/sharer\/sharer.php?u=https%3A%2F%2Fwww.defrent.de%2Fen%2F2016%2F12%2Fpreparing-ms-excel-spreadsheets-for-translation-in-your-cat-tool%2F\" title=\"Share on Facebook\" aria-label=\"Share on Facebook\" role=\"button\" rel=\"nofollow\" class=\"shariff-link\" style=\"; background-color:#79b428; color:#fff\" target=\"_blank\"><span class=\"shariff-icon\" style=\"\"><svg width=\"32px\" height=\"20px\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 18 32\"><path fill=\"#3b5998\" d=\"M17.1 0.2v4.7h-2.8q-1.5 0-2.1 0.6t-0.5 1.9v3.4h5.2l-0.7 5.3h-4.5v13.6h-5.5v-13.6h-4.5v-5.3h4.5v-3.9q0-3.3 1.9-5.2t5-1.8q2.6 0 4.1 0.2z\"\/><\/svg><\/span><\/a><\/li><li class=\"shariff-button twitter\" style=\"background-color:#ccc\"><a href=\"https:\/\/twitter.com\/share?url=https%3A%2F%2Fwww.defrent.de%2Fen%2F2016%2F12%2Fpreparing-ms-excel-spreadsheets-for-translation-in-your-cat-tool%2F&text=Preparing%20MS%20Excel%20Spreadsheets%20for%20Translation%20in%20Your%20CAT%20Tool\" title=\"Share on X\" aria-label=\"Share on X\" role=\"button\" rel=\"noopener nofollow\" class=\"shariff-link\" style=\"; background-color:#79b428; color:#fff\" target=\"_blank\"><span class=\"shariff-icon\" style=\"\"><svg width=\"32px\" height=\"20px\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 24 24\"><path fill=\"#000\" d=\"M14.258 10.152L23.176 0h-2.113l-7.747 8.813L7.133 0H0l9.352 13.328L0 23.973h2.113l8.176-9.309 6.531 9.309h7.133zm-2.895 3.293l-.949-1.328L2.875 1.56h3.246l6.086 8.523.945 1.328 7.91 11.078h-3.246zm0 0\"\/><\/svg><\/span><\/a><\/li><\/ul><\/div><p>Welcome back, dear Reader!<\/p>\n<p>This article comes straight from practice and might be interesting to translators and customers alike, because we will have a look at <strong>how to best prepare spreadsheet files for translation<\/strong>.<\/p>\n<p><!--more--><\/p>\n<p>Often, spreadsheet files &#8211; such as MS Excel, LibreOffice Calc or even plain CSV tables &#8211; are <em>generated for export\/import actions<\/em> rather than <em>written<\/em>, for example by content management systems or internationalization \/ localization (i18n\/l10n) systems for software user interfaces.<\/p>\n<p>Now, one essential thing to know about translation environment tools (Trados Studio, MemoQ, OmegaT, \u2026) is: <strong>A properly configured CAT tool will not import\/export <em>hidden text<\/em> from\/into MS Word and neither will it import\/export <em>hidden cells<\/em> from\/into MS Excel<\/strong>. This is of utmost importance, because it makes multilingual tables translatable at all!<\/p>\n<p>Clients are sending two types of spreadsheets to <a title=\"DeFrEnT Christopher K\u00f6bel - DE\/FR\/EN for IT, automation and engineering\" href=\"https:\/\/www.defrent.de\/\">DeFrEnT<\/a> for translation: Those in which original (&#8220;source text&#8221;) and translation (&#8220;target text&#8221;) are shown side by side in two columns and those in which source and target texts are presented &#8220;top-down&#8221; in subsequent rows:<\/p>\n<p><a href=\"https:\/\/www.defrent.de\/wp-content\/uploads\/2016\/12\/excel-translation-cols-1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-587 size-full\" title=\"The first step in column-based spreadsheet translation will often be to copy source to target columns.\" src=\"https:\/\/www.defrent.de\/wp-content\/uploads\/2016\/12\/excel-translation-cols-1.png\" alt=\"Excel sheet with original and translation side-by-side in columns\" width=\"640\" height=\"380\" srcset=\"https:\/\/www.defrent.de\/wp-content\/uploads\/2016\/12\/excel-translation-cols-1.png 640w, https:\/\/www.defrent.de\/wp-content\/uploads\/2016\/12\/excel-translation-cols-1-300x178.png 300w\" sizes=\"auto, (max-width: 640px) 100vw, 640px\" \/><\/a><\/p>\n<p>With side-by-side (column-based) table layouts, we will thus proceed to copy all translatable cells from from the source column(s) into the target column(s), and then <em>hide<\/em> everything else from the CAT tool by Ctrl+Selecting, for example, the columns A, B, D and F, followed by a right-click on one of those column headers and clicking on &#8220;Hide&#8221; (or &#8220;Ausblenden&#8221; in German).<\/p>\n<p><a href=\"https:\/\/www.defrent.de\/wp-content\/uploads\/2016\/12\/excel-translation-cols-2.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-588\" src=\"https:\/\/www.defrent.de\/wp-content\/uploads\/2016\/12\/excel-translation-cols-2.png\" alt=\"\" width=\"640\" height=\"212\" srcset=\"https:\/\/www.defrent.de\/wp-content\/uploads\/2016\/12\/excel-translation-cols-2.png 640w, https:\/\/www.defrent.de\/wp-content\/uploads\/2016\/12\/excel-translation-cols-2-300x99.png 300w\" sizes=\"auto, (max-width: 640px) 100vw, 640px\" \/><\/a>Then we do the same with rows 1-4 as these header cells don&#8217;t need to be translated either. We are left with only those cells that shall later contain the English translations. This also serves to protect content that the translator or his CAT tool shall not touch from any erroneous editing:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-589\" src=\"https:\/\/www.defrent.de\/wp-content\/uploads\/2016\/12\/excel-translation-cols-3.png\" alt=\"\" width=\"460\" height=\"472\" srcset=\"https:\/\/www.defrent.de\/wp-content\/uploads\/2016\/12\/excel-translation-cols-3.png 460w, https:\/\/www.defrent.de\/wp-content\/uploads\/2016\/12\/excel-translation-cols-3-292x300.png 292w\" sizes=\"auto, (max-width: 460px) 100vw, 460px\" \/><\/p>\n<p>The second, row-based type of tables is more tricky. Excel does not allow to select every second row or to copy a selection range into every second row:<\/p>\n<p><a href=\"https:\/\/www.defrent.de\/wp-content\/uploads\/2016\/12\/excel-translation-rows-1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-590\" src=\"https:\/\/www.defrent.de\/wp-content\/uploads\/2016\/12\/excel-translation-rows-1.png\" alt=\"\" width=\"620\" height=\"288\" srcset=\"https:\/\/www.defrent.de\/wp-content\/uploads\/2016\/12\/excel-translation-rows-1.png 620w, https:\/\/www.defrent.de\/wp-content\/uploads\/2016\/12\/excel-translation-rows-1-300x139.png 300w\" sizes=\"auto, (max-width: 620px) 100vw, 620px\" \/><\/a>Usually, these kind of software strings are better delivered as Gettext PO files or XML, but sometimes, we get them as Excel tables. Due to the copy-paste restriction and possibly very large files, we cannot copy all those strings by hand. What we need here, are <strong>two VB macros<\/strong>: One to copy a selection of first rows to all second rows and another macro to hide all first rows for us.<\/p>\n<p>Start by unhiding Excel&#8217;s &#8220;Developer Tools&#8221; ribbon if it is not already visible. Click on &#8220;Macros&#8221;, enter an appropriate name for your new macro and click on &#8220;Create&#8221;. The Macro console opens and you can enter the following two macros (which I have named in German, but you can change the name to whatever suits you):<\/p>\n<p><code>Sub ZweiteZellenMitErstenFuellen()<br \/>\n'This one copies first rows into second rows within a selection<br \/>\nDim Bereich As Range, Zelle As Range, Zeile As Long<br \/>\nZeile = 2<br \/>\nSet Bereich = Selection<br \/>\nWhile Zeile &lt; Bereich.Rows.Count<br \/>\nBereich.Cells(Zeile, 1) = Bereich.Cells(Zeile, 1).Offset(-1, 0)<br \/>\nZeile = Zeile + 2<br \/>\nWend<br \/>\nEnd Sub<\/code><\/p>\n<p><code>Sub ErsteZeilenAusblenden()<br \/>\n'This one hides first rows within a selection<br \/>\nDim Zeile As Long<br \/>\nZeile = 1<br \/>\nSet Bereich = Selection<br \/>\nWhile Zeile &lt; Bereich.Rows.Count<br \/>\nBereich.Cells(Zeile, 1).EntireRow.Hidden = True<br \/>\nZeile = Zeile + 2<br \/>\nWend<br \/>\nEnd Sub<\/code><\/p>\n<p>Then click on Save and <strong>save the Excel file as .xlsm<\/strong> (Excel sheet with Macros). <strong>This doesn&#8217;t need to be the client&#8217;s file. In fact, it shouldn&#8217;t!<\/strong> You can create an empty .xlsm file just to house your makros and execute them on the customer&#8217;s file as long as both files are open.<\/p>\n<p>So, with your macros saved to an otherwise empty <em>myxl8macros.xlsm<\/em> (or whatever), you&#8217;ll go back to that <strong>client file<\/strong> from the screenshot above and <strong>select column C from row 2 downwards<\/strong> (we obviously don&#8217;t want to copy the word &#8220;STRING&#8221; downwards). For some reason, you need to <strong>select one extra row beyond the last target row<\/strong> or it won&#8217;t copy the last row (i.e. select including C14 in the above example).<a href=\"https:\/\/www.defrent.de\/wp-content\/uploads\/2016\/12\/excel-translation-rows-2.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignright size-thumbnail wp-image-592\" src=\"https:\/\/www.defrent.de\/wp-content\/uploads\/2016\/12\/excel-translation-rows-2-150x150.png\" alt=\"\" width=\"150\" height=\"150\"><\/a><\/p>\n<p>Click on Macros in your Developer Tools ribbon. It should show you a list of the macros in <em>myxl8macros.xlsm<\/em> as long as &#8220;Macros from: All open Spreadsheets&#8221; is selected (see screenshot to the right). Execute <em>ZweiteZellenMitErstenFuellen<\/em> and the rows will be copied. Then execute <em>ErsteZeilenAusblenden<\/em> on the same selection: Voil\u00e0!<\/p>\n<p><a href=\"https:\/\/www.defrent.de\/wp-content\/uploads\/2016\/12\/excel-translation-rows-3.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-593\" src=\"https:\/\/www.defrent.de\/wp-content\/uploads\/2016\/12\/excel-translation-rows-3.png\" alt=\"\" width=\"617\" height=\"207\" srcset=\"https:\/\/www.defrent.de\/wp-content\/uploads\/2016\/12\/excel-translation-rows-3.png 617w, https:\/\/www.defrent.de\/wp-content\/uploads\/2016\/12\/excel-translation-rows-3-300x101.png 300w\" sizes=\"auto, (max-width: 617px) 100vw, 617px\" \/><\/a>All source text DE rows have been copied to target EN rows and DE rows are hidden. Finish by manually hiding the first title row and the A and B columns, as before. Only translatable strings are visible now.<\/p>\n<p>In both cases, whether with a column- or row-based spreadsheet, you are now ready to save your client file and import it into your CAT tool for translation. You will only get to see what needs to be translated.<\/p>\n<p><a href=\"https:\/\/www.defrent.de\/wp-content\/uploads\/2016\/12\/excel-translation-rows-4.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-594\" src=\"https:\/\/www.defrent.de\/wp-content\/uploads\/2016\/12\/excel-translation-rows-4.png\" alt=\"\" width=\"720\" height=\"521\" srcset=\"https:\/\/www.defrent.de\/wp-content\/uploads\/2016\/12\/excel-translation-rows-4.png 720w, https:\/\/www.defrent.de\/wp-content\/uploads\/2016\/12\/excel-translation-rows-4-300x217.png 300w\" sizes=\"auto, (max-width: 720px) 100vw, 720px\" \/><\/a><strong>Important:<\/strong> After exporting from the CAT tool, just remember to <em>unhide<\/em> all those hidden rows and columns by doing Ctrl+A (select all), right-click on a column\/row header and click on &#8220;Show&#8221; before you deliver the file to your customer. Most users are not used to working with hidden content and will accusingly ask why their file looks totally corrupted instead of noticing that their spreadsheet starts with column C instead of A.<\/p>\n<p>I hope this has helped you better understand how professional translators such as <em>Christopher K\u00f6bel<\/em> of <a title=\"DeFrEnT Christopher K\u00f6bel - DE\/FR\/EN for IT, automation and engineering\" href=\"https:\/\/www.defrent.de\/\" target=\"_blank\" rel=\"noopener noreferrer\">DeFrEnT<\/a> preprocess, work on and post-process spreadsheets so you can get the expected translation results. It should also show that column-based layouts are much easier to translate than row-based files.<\/p>\n<p>Do you know other tricks or tips on preparing spreadsheets for translation? Did you encounter other difficulties? Got useful IF-Formulas to selectively copy content from source to target? Leave a comment!<\/p>\n<p><span style=\"border-radius: 2px; text-indent: 20px; width: auto; padding: 0px 4px 0px 0px; text-align: center; font: bold 11px\/20px 'Helvetica Neue',Helvetica,sans-serif; color: #ffffff; background: #bd081c no-repeat scroll 3px 50% \/ 14px 14px; position: absolute; opacity: 1; z-index: 8675309; display: none; cursor: pointer;\">Merken<\/span><\/p>\n<p><span style=\"border-radius: 2px; text-indent: 20px; width: auto; padding: 0px 4px 0px 0px; text-align: center; font: bold 11px\/20px 'Helvetica Neue',Helvetica,sans-serif; color: #ffffff; background: #bd081c no-repeat scroll 3px 50% \/ 14px 14px; position: absolute; opacity: 1; z-index: 8675309; display: none; cursor: pointer; top: 1779px; left: 58px;\">Merken<\/span><\/p>\n<p><span style=\"border-radius: 2px; text-indent: 20px; width: auto; padding: 0px 4px 0px 0px; text-align: center; font: bold 11px\/20px 'Helvetica Neue',Helvetica,sans-serif; color: #ffffff; background: #bd081c  no-repeat scroll 3px 50% \/ 14px 14px; position: absolute; opacity: 1; z-index: 8675309; display: none; cursor: pointer;\">Merken<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Welcome back, dear Reader! This article comes straight from practice and might be interesting to translators and customers alike, because we will have a look at how to best prepare spreadsheet files for translation.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"activitypub_content_warning":"","activitypub_content_visibility":"","activitypub_max_image_attachments":4,"activitypub_interaction_policy_quote":"anyone","activitypub_status":"federate","footnotes":""},"categories":[4],"tags":[119,118],"class_list":["post-586","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-bd081c","tag-ffffff"],"aioseo_notices":[],"featured_image_src":null,"featured_image_src_square":null,"author_info":{"display_name":"Christopher K\u00f6bel","author_link":"https:\/\/www.defrent.de\/en\/author\/defrenter\/"},"_links":{"self":[{"href":"https:\/\/www.defrent.de\/en\/wp-json\/wp\/v2\/posts\/586","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.defrent.de\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.defrent.de\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.defrent.de\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.defrent.de\/en\/wp-json\/wp\/v2\/comments?post=586"}],"version-history":[{"count":16,"href":"https:\/\/www.defrent.de\/en\/wp-json\/wp\/v2\/posts\/586\/revisions"}],"predecessor-version":[{"id":1823,"href":"https:\/\/www.defrent.de\/en\/wp-json\/wp\/v2\/posts\/586\/revisions\/1823"}],"wp:attachment":[{"href":"https:\/\/www.defrent.de\/en\/wp-json\/wp\/v2\/media?parent=586"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.defrent.de\/en\/wp-json\/wp\/v2\/categories?post=586"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.defrent.de\/en\/wp-json\/wp\/v2\/tags?post=586"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}