Multiple replace

Undo support - NO. Save the workbook before running the command.

This command should be used in conjunction with 'Collect unique text' to make multiple replacements in a number of worksheets or selection of the active workbook.

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: Translate the second column in the file created by 'Collect unique text' or make your own replacement lists where the first column contains the original text and the second - its translation.

Select a range of cells from the 1^st and 2^nd 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 'Multiple replace' 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 Paste 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).

Options:

1. Worksheets - Select one or several worksheets that need to be processed in the active workbook 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 - 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 'Multiple replace' command.
4. Replacement List - The replacement list should be entered here. There are two ways to insert text into this field:
   

   • Prepare a replacement list in Excel. The first column should contain text that you want to 'translate': words, phrases or even complete sentences or several sentences. If the text contains line breaks, they must be entered as '<br>'. The second column should be a translation of that. Once the list is complete, copy both columns to the clipboard and click Paste or press Ctrl+V (or another key combination assigned on your system) – this will paste the text from the clipboard.
   • Enter the list manually by typing the text you want to translate, then Tab key, then the translation. Press Enter to insert a new line. Continue until the list is complete.
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 replace text in them. If you would like to exclude hidden detail rows/columns from the operation, uncheck this option.
6. 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.
   Note: The entire cell must be formatted with the specified color, not just a few letters.
7. 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) once you are done with the formatting of the translated spreadsheet.

When the appropriate options have been selected, click Replace All. To exit, click Close.

The command's progress will be shown at the bottom of the dialogue.

Notes:

• 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 AutoFit row command or change row heights using Cell Resize Wizard command, merge adjacent cells, turn Word Wrap on, etc). Use 'Highlight replacements in' option to differentiate between the changed cells and the rest of the document.
• When you copy a range into the clipboard, make sure that you do this immediately before you run 'Multiple replace'. 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 'Multiple replace' dialogue.