Upload text substitution data from an EXCEL/CSV template
The “LOAD FROM EXCEL OR CSV TEMPLATE” button on the text substitutions screen will allow you to upload your substitutions from an EXCEL spreadsheet template (.xlsx) or a CSV file (.csv, comma separated file format).
GOFR will allow you to load multiple rows of substitution data from an EXCEL spreadsheet.
Rules for the file
- To load substitutions from Excel, the data must appear in the first sheet of the workbook.
- The data must appear with appropriate headings in the first row.
- Columns can be in any order BUT they must have the appropriate headings.
- Only two columns are absolutely required: Text To Find and Text To Replace, the rest will be defaulted to some value (see “Default” in the tables below).
- The columns MUST have an appropriate heading as per the tables below. They MUST be spelled exactly as per below; upper and lower case does not matter.
- In an Excel file, a blank entry in the "Text To Find" column will be ignored by the program.
- In a CSV file the values are separated by commas; a value that contains a comma, a quote or a line break must be enclosed in double quotes.
- Text To Find and Text To Replace can each be up to 4000 characters long. Longer text is truncated and the program shows which records were truncated.
- Up to 10,000 rows are loaded.
- The loaded substitutions replace the substitutions that are currently on the screen.
- The trial version is limited to 5 substitutions by template load. The Lite version does not load from a template.
Download the template here: http://www.officefindreplace.com/Home/Downloads (the “DOWNLOAD TEMPLATE” button opens this page).
There are two templates there, one simple one without formatting fields (such as when you want to find text formatted a certain way), and one more complex one with formatting. See additional help: Find text formatted a specific way
You can also click “EXPORT TO CSV” on the text substitutions screen to get a file with all of the columns, and use it as a template.
For detailed explanation of parameters please read:
text substitution parameters help page
Text and option columns
For the True or False columns enter True or False (T, F, 1 and 0 are accepted too).
| Field Name (Must appear as the header for the column) | Default | Description |
|---|---|---|
| Text To Find | This is the text you wish to find in a document. It can be a regular expression or a wild card pattern (see further settings below). | |
| Regex Find | False | Value: True or False. If True, text to find will be treated as a regular expression pattern. See more here: Regular Expressions |
| WildCard Find | False | Value: True or False. If True, text to find will be treated as a wildcard expression. See more here: WildCards |
| Text To Replace | This is the text you wish to replace in a document. It can be a regular expression or a wild card pattern (see further settings below). | |
| Regex Replace | False | Value: True or False. If True, text to replace will be treated as a regular expression. |
| WildCard Replace | False | Value: True or False. If True, text to replace will be treated as a wildcard pattern. |
| Body | True | Value: True or False. All types (Excel, Power Point, Word, Visio, Text). When checked the body is searched for the text to find. |
| Hdr | True | Value: True or False. Word, Power Point, Excel only. When checked the header is searched for the text to find. |
| Footer | True | Value: True or False. Word, Power Point, Excel only. When checked the footer is searched for the text to find. |
| Notes | True | Value: True or False. Power Point Only. When checked, Power Point notes are searched for the text to find. The heading Foot Notes is accepted too. |
| Links | True | Value: True or False. Word, Excel, Power Point and Visio only. When checked, any hyperlinks defined in the office document are searched. Note that in EXCEL, if this option is checked, ONLY hyperlinks will be searched. The heading Hyperlinks is accepted too. |
| Case Insensitive | True | Value: True or False. When checked, the upper and lower case of the text you are searching in your document is ignored. |
| End Notes | True | Value: True or False. Word only. When checked end notes are searched for the text to find. |
| Whole Word | False | Value: True or False. All document types. When checked, the text to find is treated as an entire word, and not part of the word. See below for special characters that designate word boundaries (start and stop). |
| Entire Cell | False | Value: True or False. Excel only. When checked, entire cell must match the value to be substituted (replaced). |
| Excel Formula | False | Value: True or False. Excel only. Can not be combined with Entire Cell, they must be entered on separate lines. |
| File Name | False | Value: True or False. Apply the find and replace operation to the final file name. This only applies to the file name only, and not to the output directory part of the file name. |
| Follow Case | False | Value: True or False. Follow case (upper and lower) of the original text. |
Formatting to find columns
These columns hold the settings of the Find text formatted a specific way screen. Value: A, Y or N. The default is A.
| Field Name | Description |
|---|---|
| Italics To Find | A = Any Formatting, Y = Formatted in Italics, N = Not formatted in Italics |
| Bold To Find | A = Any Formatting, Y = Formatted in Bold, N = Not formatted in Bold |
| Underline To Find | A = Any Formatting, Y = Formatted in Underline, N = Not formatted in Underline |
| Strike To Find | A = Any Formatting, Y = Formatted with strike thru, N = Not formatted with strike thru |
| Style To Find | A = Any, Y = In Style, N = Not In Style. The style is given in StyleId To Find. |
| StyleId To Find | The name of the style, such as Heading1. The default is Normal. |
| Font Color To Find | A = Any, Y = In Font Color, N = Not In Font Color. The color is given in Font Color Id To Find. |
| Font Color Id To Find | The color as #AARRGGBB, such as #FFFF0000 for red. The default is #FF000000 (black). |
| Highlight Color To Find | A = Any, Y = In Highlight Color, N = Not In Highlight Color. The color is given in Highlight Color Id To Find. |
| Highlight Color Id To Find | The color as #AARRGGBB. Use one of the values of the Highlight Color Chart. The default is #FFFFFFFF (white). |
| Vertical Align To Find | A = Any, Y = Vertically aligned, N = Not vertically aligned |
Formatting to apply columns
These columns hold the settings of the Apply Formatting After Replacement screen. Value: A, Y or N: A = Leave As Is, Y = Apply, N = Remove. The default is A.
| Field Name | Description |
|---|---|
| Italics To Replace | Italics |
| Bold To Replace | Bold |
| Underline To Replace | Underline |
| Strike To Replace | Strike thru |
| Font Color To Replace | Font color. The color is given in Font Color Id To Replace. |
| Font Color Id To Replace | The color as #AARRGGBB. The default is #FF000000 (black). |
| Highlight Color To Replace | Highlight color. The color is given in Highlight Color Id To Replace. |
| Highlight Color Id To Replace | The color as #AARRGGBB. Use one of the values of the Highlight Color Chart. The default is #FFFFFFFF (white). |
| Vertical Align To Replace | Vertical alignment |
| Font Size To Replace | Font size. The size is given in Font Size Value To Replace. |
| Font Size Value To Replace | The font size in points, such as 12 or 10.5 (use a period as the decimal separator). |
Word boundaries
By default the following characters denote word boundaries (you can change them in “Word Boundary Identifiers” in the Advanced Settings):
+=-() /'<>?^&%$#@!;.:,[] and the non-breaking space
Special considerations
- When regular expressions or wildcards are turned on, whole word and case insensitive are set to false. They do not apply to regular expression pattern searches. When Regex Find is turned on, entire cell is set to false too.
- Whole Word and Links cannot be turned on at the same time, they are treated as mutually exclusive.
- When Regex Replace or WildCard Replace is True and neither Regex Find nor WildCard Find is True, the corresponding find option is turned on.
- When Whole Word or Entire Cell is True, WildCard Find is set to false.