Translate using Translation Table
||Undo support - NO. Save the workbook before running the command.
This command should be used in conjunction with 'Extract Translatable Text' to make multiple replacements in a number of worksheets or selection of the active workbook.
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 translation table into the drawing with Translate using Translation Table 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.
Step 1: Translate the second column in the file created by 'Extract Translatable Text' or make your own translation tables where the first column contains the original text and the second – its translation.
Select a range of cells from the 1st and 2nd columns. Press Ctrl+C or another clipboard copying key combination depending on your system. Alternatively: for Word 2003 or earlier – click Edit -> Copy; for Word 2007 or later – click Copy in Clipboard group on the Home tab.
Step 2: Switch to the workbook where replacements should be made. Run 'Translate Using Translation Table' command. In the displayed dialogue, select worksheets to process or check 'Selection' to process the selection in the active worksheet only (may be a range of cells, a textbox or a group of shapes containing textboxes). Change filtering options as explained below. Click next to the textbox below to paste the data from the clipboard (or press Ctrl+V or another key combination for pasting from clipboard depending on your system). Select 'Highlight replacements." – this will change the background colour of all replaced cells/textboxes so that you will be able to format them when translation is complete (this is necessary because the cell formatting may prevent you from seeing the entire cell contents).
Step 3: The replaced cells are highlighted (if the 'Highlight replacements' option was selected).
Once all worksheets have been checked, highlights can be removed by running Remove Highlight
or manually by setting cell/textbox fill color to 'No Fill'.
- Select one or several worksheets that need to be processed in the active workbook or click / to select / de-select all worksheets in the list.
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.
- Select this option if you want to process the selected range (in the active workbook) only. Make sure that the range is selected prior to running the 'Translate Using Translation Table' command.
- The translation table
should be entered here. There are two ways to insert text into this box:
- Use the translation table generated by the Extract Translatable Text command and fill the second column with the translation of text in the first column. Once the translation table is fully translated, copy both columns to the clipboard and click or press Ctrl+V (or another key combination assigned on your system) – this will paste the text from the clipboard.
- Enter the translation table manually by typing the text you want to translate, then Tab key, then the translation. If you need to insert a line break, use <br>. Press Enter to insert a new line. Continue until the translation table is complete.
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 replace text in them. If you would like to exclude hidden detail rows/columns from the operation, uncheck this option.
Process cells with this 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 replace text only in these cells. This option is off be default.
The entire cell must be formatted with the specified color, not just a few letters.
Highlight replacements with this color
- this option allows you to highlight the cells or textboxes where replacements were made. This is done by changing the background colour of these cells/textboxes. Make sure that you pick a colour which is not used as a background colour within the worksheets / selection. Use 'Remove Highlight
' command to remove this background color (reset to White / Transparent) when you are done with the formatting of the translated spreadsheet.
- Because the translated text may be longer than the source text, it is strongly advised to go through each worksheet that was translated with this command and format each cell individually (use or change row heights using Cell Resize Wizard command, merge adjacent cells, , etc). Use 'Highlight replacements in' option to make it easier to find the cells that were changed by the command.
- When you copy a range into the clipboard, make sure that you do this immediately before you run 'Translate Using Translation Table'. If you copy a range to the clipboard and then perform another formatting action in Excel, the program may clear the clipboard. Sometimes the clipboard will also be cleared by Microsoft Excel after the text has been pasted into the replacement window of 'Translate Using Translation Table' dialogue.