Collect unique text

Most Excel files that you need to translate contain similar information in different worksheets or workbooks. For example, if you translate an accounting report every week, you will need to translate more or less the same text. If you are given 10 similar reports at the same time, you will have to translate the same text in all of them. What if you could translate each value only once and then replace the text in all the files with the translated text? This would save you an enormous amount of time.

This command helps by extracting all unique text (cell values and textbox contents) from the selected worksheets and creating a new workbook (a replacement list ) with that text. After the replacement list is translated, you will use 'Multiple replace' command to 'translate' the text. The figures below show how this works:

Important notes

If the cells in your Excel spreadsheet have internal formatting (i.e. some words are formatted differently than other words in the same cell), this formatting will not be retained after you import the translated replacement list into the drawing with Multiple Replace command. Support for internal text formatting should be added in future versions of TransTools for Excel. In the meantime, it is recommended to restore such formatting manually.

Workflow overview:

Step 1: Run the command. Select whether to extract text from one or amore worksheets within the current workbook (check the required worksheets) or selection (may be a range of cells, a textbox or a group of shapes containing textboxes). Click Continue.

Step 2: A new workbook (replacement list) is created. The 1^st column contains unique text (cell values, textbox values) from the worksheets or selection. The 2^nd column should be filled with translation of the text in the 1^st column. The 3^rd column shows whether the text was extracted from a cell or from a textbox (as part of a shape).

Step 3: When the table has been translated, use 'Multiple replace' to translate the original files.

Additional benefits

Using Collect unique text and Multiple replace offers a number of additional scenarios for boosting your productivity:

• [SDL TagEditor users] Use SDL TagEditor to translate the replacement list. For ease of use, remove the first two rows and the third column before opening the Excel file in TagEditor. Save the translation as a new document and paste its first column into the second column of the original replacement spreadsheet;
• [SDL TagEditor users] Work with complex Excel files that produce an error in TagEditor;
• [Wordfast Classic] Use Microsoft Word to translate the replacement list (1^st column) and then paste the cleaned text as the second column back into the Excel replacement list.

Options:

1. Worksheets: Select this option to extract text from one or more worksheets in the current (active) workbook. Check the appropriate worksheets or click All / None to select / de-select all worksheets in the list.
2. Look in print area only: If this option is checked, the command will only process cells in the print area. This is useful if your spreadsheet contains a lot of text, but you only need to translate text that will be printed.
3. Selection: Text will be extracted from the selection (may be a range of cells, a textbox or a group of shapes containing textboxes).
4. Formulas: Collect text from cells that contain formulas (i.e. instead of formulas the command will extract the resulting text).
5. Process hidden groups and outlines: Sometimes the Excel spreadsheet will contain grouping and detail rows/columns. Show example

   

   
   By default, if the worksheet contains grouped (hidden) detail rows/columns, the command will collect text from them. If you would like to exclude hidden detail rows/columns from the operation, uncheck this option.
6. Process cells with specified font color: This option will allow you to process only those cells whose font color matches the specified color. For example, you may format cells that need to be processed with a specific font color, and then use this option to collect text only from these cells. This option is off be default.
   Note: The entire cell must be formatted with the specified color, not just a few letters.
7. Replacement List Format: Use the options under this tab to configure the format of the replacement list.
   
   
   
   You can select the desired format of the replacement list (Microsoft Word or Microsoft Excel) and whether to output additional information in the generated replacement list (file name, file path, column headings, type of text object).

Click Continue to complete the process. Click Cancel to exit the dialogue.

Note:

• The command does not extract text from embedded objects or Excel charts.