You can search for, and optionally replace, values in a spreadsheet using a number of methods.
Find lets you look for characters or words within a spreadsheet. You can match uppercase and lowercase letters and search forward or backward from the insertion point.The direction of the search is column by column, i.e. the search proceeds down a column until it reaches the last cell and then starts on the first cell on the next column. If a selection (row/column/range) has been made you can restrict the search to within the selected cells.
- From the menu select Edit | Find.
- In the Find what field, type your search term.
- Set other options as required then click Find Next.
Repeat as required to find other occurrences of your search value.
| Match whole word only | This searches for an exact match to your string. For example if you type ‘Part‘, the search will not find ‘Partner‘ or ‘Partnership‘. If you deselect this option Genstat will find any string that matches. |
| Match case | The search is case sensitive e.g. if you type ‘Acute‘ the search will not find ‘acute‘ as the first character is not upper case. |
| Wrap at end of sheet | When selected, a search will continue from the start of the selection, sheet or or book when it reaches the end. |
| Search by | Columns – Search up or down through columns. Rows – Search across or backwards through rows. |
| Find in | Sheet – Search within the current sheet in the spreadsheet. Book – Search all sheets in the spreadsheet book. This option is only available if a spreadsheet contains more than one sheet. Selection – Search within the current selection in the file or spreadsheet. This option is only enabled in the text file or spreadsheet has a selection active. |
| Direction | Up – Search up towards the top of a file. Down – Search down towards the bottom of a file. |
| Find next | Search for the next occurrence of the characters specified in the Find what field. |
Repeats the last search without opening the Find dialog.
Replace lets you look for characters or words within a spreadsheet and replace that text with a new piece of text. You can match uppercase and lowercase letters and match whole words only. If a selection (row/column/range) has been made you can restrict the search to within the selected cells. Additional options are available for a spreadsheet including the ability to search down columns or across rows and continuing onto other sheets within a spreadsheet book.
- From the menu select Edit | Replace.
- In the Find what field, type your search term.
- In the Replace with field, type the replacement characters.
- Set other options as required then click Replace or Replace all as necessary.
| Match whole word only | This searches for an exact match to your string. For example if you type ‘Part‘, the search will not find ‘Partner‘ or ‘Partnership‘. If you deselect this option Genstat will find any string that matches. |
| Match case | The search is case sensitive e.g. if you type ‘Acute‘ the search will not find ‘acute‘ as the first character is not upper case. |
| Wrap at end of selection | When selected, a search will continue from the start of the selection, sheet or or book when it reaches the end. |
| Replace multiple occurrences within a cell | When selected, every occurrence of the text specified in the Find what field will be replaced, otherwise only the first match within a cell will be replaced. For example, if a cell contains the characters ‘111’ this option will ‘1’ with ‘2’, which will result in ‘222’ and when this option is not selected the replaced text would result in ‘211’. |
| Search by | Columns – Search up or down through columns. Rows – Search across or backwards through rows. |
| Replace in | Sheet – Search within the current sheet in the spreadsheet. Book – Search all sheets in the spreadsheet book. This option is only available if a spreadsheet contains more than one sheet. Selection – Search within the current selection in the file or spreadsheet. This option is only enabled in the text file or spreadsheet has a selection active. |
| Find next | Search for the next occurrence of the characters specified in the Find what field. |
| Replace | Replaces the current or next instance of the characters specified in the Find what field with the text in the Replace with field. |
| Replace all | Replaces every occurrence of the characters specified in the Find what field with the text in the Replace with field. |
Use this to move the cursor to any cell within a spreadsheet. The cursor location and size of the spreadsheet are displayed at the bottom right of the status bar. The image below shows that the current spreadsheet has 200 rows and 8 columns. The cursor is in row 198, column 6.
- From the menu select Edit | Go To.
- Enter a row number and select the column name from the dropdown list (or alternatively, you can enter the column number).
- Click OK to to directly to the cell at the row/column junction.
Go back to the previously selected cell (up to 5 locations are recorded). On reaching the end of the location stack, it returns to the position at the top of the stack (i.e. the cell from which the Go back process was started).
- From the menu select Edit | Go Back.