Find Change From Spreadsheet
Script for Adobe InDesign
Latest update 11/4/2023, version 6.1
The script reads a spreadsheet of find/change pairs and replaces each instance of matching text in the active document. A virtually unlimited number of find/change pairs are performed in a single operation rather than countless visits to the InDesign Find/Change dialog.
- Find/Change text or GREP patterns
- Find/Change character and paragraph styles
- Keep capitalization when case insensitive
- Confirm each change
- User-configurable localization
NOTE: after video production, features have been added in response to user requests: CSV delimiter comma or semicolon; find/change character and paragraphs styles; additional InDesign find/change options; accept data format tab-delimited text. See instructions below for details.
Before using the script
The script requires a spreadsheet of find/change pairs, saved as Comma Separated Values (CSV format) or tab-delimited text. The spreadsheet must have at least two columns, one for something to find, another for its replacement. Columns may include character and paragraph styles, which are optional. Style columns to find that are omitted, or the cell empty, are equivalent to selecting “Any Style” in the InDesign Find/Change dialog. Style columns for change, if empty, are ignored, otherwise matches to the find column of the row are assigned the style name listed. If the style does not exist, it is created and may be later configured for the desired formatting.
The first row is the header, which identifies the contents of each column. In this example, the column headers are Find, Change, and styles to find and change. Using this data file, the script changes "business" to "company" and assigns to it the character style bold, "button up" is replaced with "finish", styled bold and its paragraph changed to the style body text, and every instance of "favour" becomes "favor", assigned the character style emphasis.
These columns could just as well be labeled “match”, “replace,”, “find c style,” etc., or any other desired column names. The precise column names, or their order, are not important, and there is no harm in extra columns. Which columns are the text to find and change, and styles to find and change, are selected in the script interface.
In each row, the values for find and change can be either text or a regular expression (GREP). Rows may differ even, some text, some regular expressions. To inform the script that the text to match is a regular expression, begin and end the expression with forward slashes. Flags after the trailing slash are ignored, as the script always replaces all instances (g flag for global), and whether to ignore letter case (i flag for insensitive) is controlled by the script interface option Case insensitive. If the expression includes any capture groups, the normal placeholders are recognized in the replacement, i.e. $1, $2, etc. In the rare case the text to find happens to begin with, and end with, a forward slash, there is no choice but to use a regular expression, because the script always sees this as the signal for a regular expression. In that case, construct a regular expression that matches the desired text but escapes the slashes and any other punctuation characters that could be interpreted as regular expression commands.
Values in the find column of the spreadsheet are equivalent to values entered into the Text tab or the GREP tab of the InDesign Find/Change dialog. All special characters available in the dialog are recognized by the script.
To find and/or change the character style [None], or paragraph styles [No Paragraph Style] or [Basic Paragraph], use the style names as listed, which match how each appears in the InDesign interface, both the Styles panel and Find/Change dialog. As well, the script recognizes [Any Style] as does the Find/Change dialog, although using this value is unnecessary. If the find value for either style is blank, the script interprets that as any style. If the change value is blank, no change occurs.
How to use the script
The interface is divided into three sections: Data file, Columns, and Options. Click the File button to select the data file, choose the data columns and options, and click the OK button to begin. The user is notified when processing is complete, or if an error occurs.
Section 1: Data File
CSV Delimiter — the character that separates columns of CSV data (does not apply to tab-delimited text). The default is comma, normal in the United States. Some European countries use semicolon rather than comma. Select the delimiter used in your region of the world.
File — select the data file, CSV or TXT, then the column drop-down lists (next) are loaded with the columns discovered in the data.
Section 2: Columns
Text/GREP — to the right are drop-down lists to select the column in the data that is the text or GREP pattern to find, and the column that is the replacement.
Character Style — to the right are drop-down lists to select the column in the data that is the character style to find, and the column that is the new style. A style to find is not required. When unset or the column in empty, a match is determined only by the find column of the data, and once replaced, the matching or replaced text is assigned the character style specified in the character style change column.
Paragraph Style — to the right are drop-down lists to select the column in the data that is the paragraph style to find, and the column that is the new style. A style to find is not required. When unset or the column in empty, a match is determined only by the find column of the data, and once replaced, the entire paragraph in which the match appears is assigned the paragraph style specified in the paragraph style change column.
Section 3: Options
Options are identical to those found in the InDesign Find/Change dialog, except for Keep capitalization, exclusive to the script.
Include — choices are Locked layers/objects, Locked stories, Hidden layers/objects, Parent pages, and Footnotes. Check items to include. Uncheck items to ignore.
Whole word — text matches only when whole words. If disabled, text matches also when words contain the text. For GREP, this option is achieved by wrapping the GREP pattern in \< and \>, which indicates whole word. The same could be done to patterns in the data and get the same result. The option exists to simplify the data file if desired. IMPORTANT: do not use this option if GREP patterns in the data file already contain the command for whole word. Doing so prevents patterns from matching.
Case insensitive — text matches regardless of upper or lower case, or any combination of either. For GREP, this option is achieved by prefixing the GREP pattern with (?i), which indicates case insensitive. The same could be done to patterns in the data and get the same result. The option exists to simplify the data file if desired. IMPORTANT: do not use this option if GREP patterns in the data file already contain the command for case insensitive. Doing so prevents patterns from matching.
Keep capitalization — available when the option Case insensitive is enabled. When the text found is capitalized, the change is capitalized even if not capitalized in the spreadsheet. If the text found is not capitalized, the spreadsheet data is used as-is. This is useful for words that are normally not capitalized, but could appear at the beginning of sentences, requiring capitalization. Without this option, such cases require two separate find/change pairs, one capitalized, another not, and processed with the Case insensitive option disabled, otherwise risk the beginning of sentences becoming lowercase.
Confirm each change — if enabled, the display is magnified to the value for the option Zoom (next), to better show the changes to confirm. A confirmation dialog appears on screen, described in the next section.
Zoom — the percentage to which the display is magnified when processing begins. This allows the user a closer look at text to change and better judge if changes are acceptable.
Confirm each change
Each match is selected in the layout, and a confirmation dialog displays the proposed change above a series of buttons:
Zoom: - / + — click minus to decrease zoom and show more of the page. Click plus to increase zoom for closer inspection of the change to confirm.
OK — the selected text is changed as indicated, and the next proposed change is selected.
OK all — the selected text and all remaining matching instances are changed without further user intervention. This applies only to the current find/change pair. When the next find/change pair begins processing, the confirmation dialog is again displayed. This repeats until all find/change pairs are processed.
Skip — the selected text is not changed, and the next proposed change is selected.
Skip all — the selected text is not changed and all remaining matching instances are ignored. Processing resumes with the next find/change pair and again the confirmation dialog is displayed.
Cancel — processing ceases without further changes. Any changes previously accepted remain. If desired, the Edit menu item Undo restores the document to its state prior to launching the script.
When character or paragraph styles change, the proposed style or styles are also displayed.
The confirmation dialog appears centered on screen and near the top of the window. If the dialog obscures the layout, it may be moved to another location on screen, even to a secondary display. The dialog will maintain its new position until the next launch of the script.
If any story has overset text, it is not possible to show the proposed change because it is hidden off-screen in the overset text. In this case, a warning is displayed and the user has the option to continue regardless, or decline and remedy the overset text before trying again, the recommended choice. Then each change is visible and can be confirmed.
By default the script language is US English, which does not require further download or configuration. To have the script interface display other languages, choose from the available languages below. Download and copy the .i18n file to the script folder alongside the script. When launched, the script detects the language file and displays interface text in that language. If your language is not listed, download the English file and translate it. The file is plain text formatted as JSON, containing interface text in English, and a second value for its translation, which for the English file is the identical text. Copy the file and rename it to replace “en” with the relevant code for your language, then edit the file to change each line’s second value to the translation in your language. For more detailed instructions of how to edit and install i18n files, see How to Localize Scripts.
FREE 30 DAY TRIAL
Single-user perpetual license
Pay once, no subscription, use forever
Change log: find-change-from-spreadsheet.txt
For help installing scripts, see How to Install and Use Scripts in Adobe Creative Cloud Applications.
IMPORTANT: scripts are developed for the latest Adobe Creative Cloud applications. Many scripts work in CC 2018 and later, even some as far back as CS6, but may not perform as expected, or run at all, when used in versions prior to 2018. Photoshop features Select Subject and Preserve Details 2.0 definitely fail prior to CC 2018 (version 19) as the features do not exist in earlier versions. For best results use the latest versions of Adobe Creative Cloud applications.
IMPORTANT: by downloading any of the scripts on this page you agree that the software is provided without any warranty, express or implied. USE AT YOUR OWN RISK. Always make backups of important data.
IMPORTANT: fees paid for software products are the purchase of a non-exclusive license to use the software product and do not grant the purchaser any degree of ownership of the software code. Author of the intellectual property and copyright holder William Campbell retains 100% ownership of all code used in all software products regardless of the inspiration for the software product design or functionality.