Actions

This tab determines the text to be searched for, or search/replaced.

Generate a Hyperlink report

The Link Report is enabled when the Generate a hyperlink report checkbox is checked, AS WELL AS at least one search replace being performed in the Hyperlink address field. It displays both the original and modified urls which is very useful to check new links before they are changed.

Load replacement list from file

When checked, search/replace pairs are loaded from a file dynamically at run time - from Excel (.XLS files), Comma Separated Value (.CSV files) or Tab-delimited (.TAB files) formats. The current search type, search location and other options are used for every search/replace pair added. A list can also be imported using an option in the Options menu.

For the complete file format details, please click here. You can find sample import files in the Samples folder (in .tab, .csv and .xls format).

ExcelPipe can handle an unlimited number of replacements - but Microsoft Excel is limited to 65534 rows of data. You can work around this limit by loading multiple files from the command line. Note - despite Microsoft Excel 2007 and later being able to handle 1 million rows, it is unable to provide data beyond 65535 rows to ExcelPipe. To work around this, save your Microsoft Excel data to a .CSV or .TAB temporary file so that ExcelPipe can import all the data.

Note: Loading large lists from Excel is very slow - export your list to .CSV or .TAB and then import that instead.

Using these values

Uses the search/replace pairs specified in the grid. Click the [Add] button to add a new row to the end of the grid. To delete rows, first select them and then click [Delete]. To edit a row, click it, and then press [F2], or click the cell a second time. You can [Tab] and [Shift-Tab] through the grid. You can change the search settings for multiple rows simultaneously by first selecting them and then changing the search settings below. You can move rows using drag and drop. Copy rows by selecting them and then clicking [Copy]. Clear All rows by clicking [Clear All].

Find what

Type the information you want to search for or paste it from the Clipboard. The Find text is limited by Microsoft Excel to be a maximum length of 255 characters. You can right click this field for a popup menu with Cut, Copy, Paste and Clear commands.

You can search for more than one line of text at a time. If you use the wildcards *, ? or ~ in your Find What string, see the special characters section.

Searching for multiple lines

Although the edit field can hold multiple lines of text, Microsoft Excel does NOT allow you to search for multiple lines at once (and ExcelPipe cannot get around this limitation). The best way to search for multiple lines is to break them up into several line-by-line or phrase-by-phrase searches.

Replace with

Type the text that you want to use as replacement text or paste if from the Clipboard. Leave the Replace With box empty to delete the Find What box from your document. You can right click this field for a popup menu with Cut, Copy, Paste and Clear commands.

Search Type

Normal search

Matches the text exactly as written.

EasyPattern wildcards

Important Note: This search type does NOT apply to the Cells search location. For wildcard searches in Cells, use Microsoft Excel's inbuilt wildcards, with the Search Type set to 'Normal search'.

An EasyPattern allows you to search for a class of text strings rather than specifying every single possibility. For example, to search for a dollar sign followed by a currency amount:

$[ capture(1+ digits, period, 2 digits) ]

Replace With:

USD $1

Click here for the EasyPattern basics and reference.

EasyPatterns are easier to learn and use than perl regex patterns, but to use them in other packages you need to use EasyPattern Helper to convert them to perl regex.

Perl regex wildcards

Important Note: This search type does NOT apply to the Cells search location. For wildcard searches in Cells, use Microsoft Excel's inbuilt wildcards, with the Search Type set to 'Normal search'.

A perl regex pattern allows you to search for a class of text strings rather than specifying every single possibility. For example, to search for a dollar sign followed by a currency amount:

\$(\d+\.\d{2})

Replace With:

USD $1

Click here for the Perl regex reference.

Perl regex patterns are harder to learn and use than EasyPatterns, but they are support by a huge range of applications.

Search location

• Cells - look in each cell
• Hyperlink addresses (*) - look inside hyperlink addresses. To change the displayed text, use the 'Cells' option above as well
• Shapes/Shapes and Text Boxes (*) - inside the text of shapes and grouped shapes and text boxes
• Headers (*) - search inside the left, centre and right header of each sheet
• Footers (*) - search inside the left, centre and right footer of each sheet
• Built in properties (*) - inside Subject, Title, Author etc fields
• Custom properties (*) - inside custom properties defined by the user
• Links (OLE and DDE) (*) - inside links to external documents and databases, and the connection strings of Query Tables and Pivot Tables - ADO, DAO, OLEDB and ODBC database queries, Web queries etc.
• Chart Titles (*) - inside the titles of charts. Note - axis labels come from the work sheet itself, so use the Cells option to replace them
• Defined Names (*) - search and replace the name and the value of predefined names (the names of ranges, constants etc)
• Code Modules (*) - search and replace code modules. If the modules are password-protected, enter this password on the Options tab, Password to Unprotect field. See code modules security.
• Sheet Names (*) - search inside sheet names e.g. Sheet1, Sheet2, Sheet3

Items with (*) support the use of EasyPatterns and Perl regex patterns.

Hyperlink addresses

If you want to change the displayed text (not the hyperlink itself), please use a Cells Search instead of, or in addition to this option. This option can be used to search and replace hyperlinks to update server names, UNC paths etc. We recommend that the Match Case option be unchecked, so that both upper case and lower case data can be found. You can use any search type, including EasyPatterns and Perl regex patterns.

If the hyperlink address and display text are identical they will be updated simultaneously. ExcelPipe treats the two representations of space (' ' and '%20') as equivalent, however other escaped characters (in %XX form) need to be handle manually.

Note also that sometimes the display text of a hyperlink is completely different to the actual link. To be sure that what you are replacing is correct, right click on the hyperlink in Excel and choose 'Edit Hyperlink'. You may have to perform one search for the display text and another search for the hyperlink text.

Match case

Distinguishes between UPPERCASE and lowercase letters. When Match Case is selected, ExcelPipe finds only those instances in which the capitalization matches the text you typed in the Find What box.

Match entire cell contents

Searches for matches where the entire cell has this value, rather than part of the cell.

Prompt

When checked, displays the found text and asks for confirmation before replacing it.

You can choose to Replace or Skip just one, the remainder in the current file, or the remainder for the whole job.

Note: Prompting is not supported for every Look In location.

Skip Prompt if Identical

If the search text is identical after replacing it, then skip showing the prompt. This can be very useful when using pattern matching which may not alter the text.

Highlight replacement

Where possible, the replaced text is highlighted to make it easier to find. If you use Microsoft Excel 2003 or earlier, the highlight color can only be one of 56 standard colors, so the color you choose is automatically matched to the closest available color.

Match half/full width forms

When checked, Unicode (double-byte) characters match only Unicode (double-byte) characters. When unchecked, Unicode (double-byte) characters match their ANSI (single-byte) equivalents.

You can use this argument only if you've selected or installed double-byte language support in Microsoft Excel, such as for Japanese.

[Start replacing (F9)]

When you click this button or press [F9], ExcelPipe will begin making replacements.

[Start searching (F5)]

When you click this button or press [F5], ExcelPipe will begin searching for the search text. No replacements will be made.