Finding and Replacing Data

Formula One for Java allows you to find and replace data within the active worksheet. If you wish to locate a particular string of data in a worksheet, formula, or value, you can use the Find dialog box. If you wish to search for data in Formula One for Java worksheet and replace it with other data, you have the option of using the Replace dialog box.

Note Formula One for Java only finds or replaces strings (values, text, and formulas). You cannot find or replace cell formatting.

To display the Find dialog box in the Workbook Designer:

Invoke the Workbook Designer.
Choose Edit > Find.

The Find dialog box appears.

To display the Replace dialog box in the Workbook Designer:

Invoke the Workbook Designer.
Choose Edit > Replace.

Click on Replace with the Find dialog box open.

The Replace dialog box appears.

The notes in the following table describe each text box and button in the find and replace dialog boxes and explain their use.

Find What text box

This is the search string you want to find or find and replace..
Enter the search string you wish to locate. You may enter values, text, or formulas.

Replace With text box

This is the replacement string you want to replace the search string.
Enter the information you want in the replacement string. You may enter values, text, or formulas.

Find Next button

This button finds the next occurence of the search string in the Find What text box.
Clicking this button makes the cell that contains the next matching text string the active cell. After using the mouse to click the button once, you may use ALT + F or the spacebar instead of clicking on the button to find other occurences of your text string.

Close button

This button closes the dialog box.
The dialog box will also close if you press Enter before clicking the Find Next button.

Replace button

Initially, this button adds the Replace With text box and Replace All button.
Click the Replace button or ALT+R or SPACEBAR to replace each occurence of the search string with the replacement string.

Replace All button

This button allows you to replace all occurences of the search string with the replacement string.
Click this button or press ALT+A to replace all occurences of the search string with the replacement string.

Search drop down list

This list allows you to choose to search by rows or by columns.

By Rows. Setting Search to By Rows means rows are searched successively by row from the active cell to the bottom of the spreadsheet, then from the top row to the active cell.

By Columns. Setting Search to By Columns means columns are searched successively by column from the active cell to the rightmost column, then from the leftmost column to the active cell.

Look In drop down list

This list allows you to choose to search in values only or formulas only.

Values. Setting Look In to Values means only search strings in values (not the text of formulas) will be found. .

Formulas. Setting Look in to Formulas means only search strings found in the text of formulas are searched for the matching text string.

Match Case buttonMatches upper and lowercase letters Click this button to find only strings with matching upper and lowercase letters. A search for Fin will not find fin.

Find Entire Cells Only buttonOnly finds cells that contain the entire text string and nothing else. Click this button to find only cells that contain the entire search string, no more, no less. A search for Fin will not find Finland.

Find What text box This is the search string you want to find or find and replace..	Enter the search string you wish to locate. You may enter values, text, or formulas.
Replace With text box This is the replacement string you want to replace the search string.	Enter the information you want in the replacement string. You may enter values, text, or formulas.
Find Next button This button finds the next occurence of the search string in the Find What text box.	Clicking this button makes the cell that contains the next matching text string the active cell. After using the mouse to click the button once, you may use ALT + F or the spacebar instead of clicking on the button to find other occurences of your text string.
Close button This button closes the dialog box.	The dialog box will also close if you press Enter before clicking the Find Next button.
Replace button Initially, this button adds the Replace With text box and Replace All button.	Click the Replace button or ALT+R or SPACEBAR to replace each occurence of the search string with the replacement string.
Replace All button This button allows you to replace all occurences of the search string with the replacement string.	Click this button or press ALT+A to replace all occurences of the search string with the replacement string.
Search drop down list This list allows you to choose to search by rows or by columns.	By Rows. Setting Search to By Rows means rows are searched successively by row from the active cell to the bottom of the spreadsheet, then from the top row to the active cell. By Columns. Setting Search to By Columns means columns are searched successively by column from the active cell to the rightmost column, then from the leftmost column to the active cell.
Look In drop down list This list allows you to choose to search in values only or formulas only.	Values. Setting Look In to Values means only search strings in values (not the text of formulas) will be found. . Formulas. Setting Look in to Formulas means only search strings found in the text of formulas are searched for the matching text string.
Match Case buttonMatches upper and lowercase letters	Click this button to find only strings with matching upper and lowercase letters. A search for Fin will not find fin.
Find Entire Cells Only buttonOnly finds cells that contain the entire text string and nothing else.	Click this button to find only cells that contain the entire search string, no more, no less. A search for Fin will not find Finland.

Note You can view and load other worksheets and workbooks while the Find/Replace dialog boxes are active, but the Find and Replace functions only operate on the worksheet that was active at the time Find or Replace was invoked.