Worksheet Area

Find and Replace

Find and Replace in a column
Do not confuse this with the 'Search Filter'. There is no connection.

Find and Replace can find and append, prepend or replace text in a selected column.

To open the find and replace dialog press Ctrl+F
or Choose 'Find and Replace' from the edit menu.

Using the Find and Replace dialog

1. Choose a search method.
Search methods.
EXACT MATCH: Search criteria must match the entire cell contents exactly.
BEGINS WITH: Search criteria must match the beginning of cell contents.
CONTAINS: Search criteria can be anywhere in the cell.
REGEX: Search criteria regex patterns should be grouped using brackets (). The 'replace' text groups should match the regex pattern.

2. Choose a Replace method.
Replace Methods.
REPLACE with: Replaces any match found with the 'replace text'
APPEND with: Appends any match found with the 'replace text'
PREPEND with: Prepends any match found with the 'replace text'

3. Choose a column to search.

4. In the Search' field
Enter the search criteria.

5. In the Replace field
Enter the text to replace, append or prepend here.

6. Click 'Find/Replace'

When a search method of 'BEGINS WITH' or 'EXACT MATCH' is selected, any replaced, appended or prepended text is applied to the entire cell contents.

The search method 'CONTAINS' will replace only the found text. Append/Prepend is applied to the found text and then inserted into the cell contents.

Example for 'APPEND' using 'CONTAINS'
Search for ' ABC ' and Append with ' DEF '
If cell contains: ' ABCGH ' then result = ' ABCDEFGH '.

Note: When using 'BEGINS WITH' or 'EXACT MATCH', you can use a * to 'APPEND' or 'PREPEND' all cells.
The 'replace text' will then be added to contents of ALL cells in the chosen column.


Split and Merge

Split and Merge Column Data
This action cannot be undone after it has been confirmed
splitmerge_dialog.jpg
How it works
Split and Merge will make a single split in a columns contents and merge the data from the split into another column. By default a 'space' character is used as the split criteria.
Note: The split is applied down the complete column contents. There is no option to split a single cell.

When choosing how to split
FROM RIGHT: This will split at the first matching separator from the right of cell contents.
FROM LEFT: Split at the first matching separator from the left of cell contents.
ALL: This will take all the cell contents.
REVERSE: This will split and reverse the cell contents, and then write the result back into the originating cell.

The split is always at the first matching separator.
Example: Split from right, using 'space' with an empty merge cell, Split cell contents = Mary Jane Thomson
Results: Split cell= Mary Jane , Merge cell = Thomson .

When choosing how to merge.
PREPEND: Merge to begining of cell contents.
APPEND: Merge to end of cell contents.

The separator character is normally preserved during the split. Unselect 'Preserve separator' to remove the separator. This option effects the split cell and the merge cell.

A space is added during the merge. To remove the 'space', select 'Merge without space'.

How to open the split and merge dialog
Choose 'Split and Merge' from the edit menu, or right-click in a cell when in edit mode and choose from the popup menu.

The Split/Merge dialog will disable non related options when selections are made. For example:- When choosing 'REVERSE' the merge options will be disabled.

The Split and Merge dialog

1. Choose a column to split.
The cell contents are split at the first matching separator found in the cell contents.
Example:-
Split cell contains: Mary Jane Thomson
Split on 'space' (from right) and merge into empty cell)
Split results: Split cell= Mary Jane, Merge cell = Thomson.

2. Choose a column to merge into
The merge column can be any column in the worksheet

3. Choose how to split
FROM RIGHT: Split from the right of cell contents.
FROM LEFT: Split from the left of cell contents.
ALL: This will take all the cell contents.
REVERSE: This will split and reverse the cell contents, and then write the result back into the originating cell.

4. Choose how to merge.
PREPEND: Merge to beginning of cell contents.
APPEND: Merge to end of cell contents.

5. In the 'Separator' field
Enter the split criteria. Just leave empty to use the default separator (space).

6. Preserve separator
The separator character is normally preserved in the cell contents after the split.
Unselect this if you want to remove the separator character during the split.
This option effects the split cell and the merge cell.

7. Merge without 'space'
A space is normally added between the merged text and the cell contents.
Select this only if you do not want a space when merging. This selection effects only the merge cell.

8. Click 'Split/Merge'

When a Split/Merge is applied, a confirmation dialog will appear. Check that the split was successful BEFORE you confirm. If you answer 'Yes' it cannot be undone. If you answer 'No', the column data will be restored to the way it was before the split.


The Search Filter

Do not confuse this with 'Find and Replace'. There is no connection .

Opening the search box
Look at the bottom right corner below the worksheet. You will see a button with a magnifying glass and arrows. Click it and a search box appears.

Closing search
To the left of the search box is a button with arrows. Click this to close the search box and cancel all filtering.

How to search
To search anywhere in the worksheet, just type in the criteria and press 'Enter' or click the button to the right of the search box.
The search is not case sensitive.
The search will look for the search criteria anywhere in a cell.
For example: 'Rob' will match Robins,Robinson,Roberts etc.

Any records that match the search criteria will be shown. All other records will be hidden. Don't panic. All your records are still there, you just can't see them.

Searching in a specific column
You can specify the column(s) you want to search.
To specify a column, use 'column_name==criteria'.
Notice that double equals are used ( == ).

Example: Surname==hardy will return only records where 'hardy' was found in the 'Surname' column.

Multiple column can be specified by seperating the criteria with commas.
Example: FirstName==john, Surname==hardy
This will search for any records that have 'John' in the 'FirstName' column AND 'Hardy' in the 'Surname' column.


Notes can be attached to any hyperlink column.
A link to the note is automatically added to the cell. The link may be activated whilst editing a cell to allow viewing and modifying the note.

Notes found in columns to be used as NOTE or SOUR in gedcom file export will be embedded into the gedcom, and are not required when tranfering the gedcom.
In all other cases the notes (txt files) need to be available to the worksheet.

Note Editor
The note editor is a simple plain text editor. The note files should always be saved with a .txt extent.

Special Key Actions in note editor CTRL+A Select all
CTRL+C Copy selected text
CTRL+V Paste selected text
CTRL+X Cut selected text
CTRL+Z Undo
CTRL+SHIFT+Z Redo
PgUp Page top
PgDn Page bottom
Home Page left
End page right
Backspace Erase to left
Del Erase from right


If you have a need to link back to multiple images, it is possible to have any number of hyperlinks in a single cell.

How to put multiple Hyperlinks in a single cell.
This function uses a base path with a hash (#) place holder, and then a list to be used as links. Separate everything with commas. Each link will consist of the text before the # , and the '#' will be replaced by each item in the comma separated list.
Examples… If you add a hyperlink to the cell containing ./images/myImage#.jpg,1,2,3 it will export as 3 separate links (1 2 3)
The links will go to ./images/myImage1.jpg, ./images/myImage2.jpg and ./images/myImage3.jpg

If you add a hyperlink cell containing http://www.myweburl.com/#, image.jpg, file.pdf, webpage.html it will export as 3 separate links with names image.jpg, file.pdf and webpage.html
These links will go to
http://www.myweburl.com/myweburl.com/image.jpg
http://www.myweburl.com/myweburl.com/file.pdf
http://www.myweburl.com/myweburl.com/webpage.html

If the urls are all different, do it this way…
http://www.#,firstwebsite.com/image.jpg,secondwebsite.com/image.jpg

The result is 2 links…
http://www.firstwebsite.com/image.jpg
http://www.secondwebsite.com/image.jpg

 


Predefined Picklists

The PickList Files
The picklists are just text files, one item per line. They are stored in the 'GenScriber/include/picklists' folder.

Picklists must have a '.txt' extent, and must be pure ascii text. (Use notepad++ to create them)
Give the lists meaningful names. They will be used in a dropdown selection later.

Note: Whenever the picklist files are changed, GenScriber must be restarted to show the changes in the dropdown lists.

Adding predefined picklists
1. Copy your predefined picklist files into the folder 'GenScriber/include/picklists'
2. Start GenScriber (if it was already running, then restart)
3. All picklists will now be available in the 'Worksheet Options' as a dropdown list in the 'FixedList' column.

The 'FixedList' column only shows when a worksheet does not have an associated master template, or if you over-ride template protection.

If you turn off 'Template Protection' in 'Preferences', GenScriber will allow you to make any changes you like to all templates.

If you want to hide the 'FixedList' column, then you need to edit the master template manually, and change 'PICKLISTDROP=1' to 'PICKLISTDROP=0' in the template header.

If you want to use different predefined lists in another template, using the same column layout, you need to make the template unique.
You can do this, and still show the same column titles to transcribers.
See Adding a unique ID to a column .

Note: when you select a 'FixedList', you must also set the 'Picklist' option to 'Yes'.


Keep picklists to a reasonable size.
Remember, not everyone has this months power processor.
Large picklists will slow down GenScriber, and on older computers, it may even become unusable.

What is a reasonable size?
Good question. The first thing to consider, is how many picklist are you using? All lists are held in memory, so the total number of items has to be taken into account.
Keep in mind also, that the user may turn on Predict and AutoComplete for the cell. This draws a lot of processing power on large lists.

I would say from my experience, That on a PC with 3GB ram, a total of 12000 items would be a maximum. Provided that there are less than 3000 items in a single list.
On less powerful systems this would reduce considerably.

I would suggest, try keeping each list below 1000 items, and only use when really necessary.


Comma and Quote Replacement

By default, commas and quotes are allowed in data cells.

The use of commas and quotes can be prevented, or replaced by another character.
This option is set in 'Preferences' (or by editing the genscriber.ini file manually).

Changing in Preferences.

1.
Select Tools > Preferences from the main menu.
2. Select the 'Worksheet' tab.
3. Choose the required actions in the 'Replace Commas' and 'Replace Quotes' options.

Options are automatically save when you close preferences.


Editing the genscriber.ini file manually.

COMMA=0comma will be removed.
COMMA=1commas are allowed.
COMMA=# replace comma with the ascii character code #.

The# value may be any ascii code 32 to 126.
Example:COMMA=59 (59 is the semicolon).
Example:COMMA=32 (32 is a space).

Note: Default value is 1. (allow commas)

IMPORTANT: Do not use any of the microsoft editors to edit the genscriber.ini file. That includes Notepad.
There are several free text editors available that will not corrupt documents. Get One.

QUOTE=0double quote will be removed.
QUOTE=1double quote is allowed.
QUOTE=# replace double quote with the ascii character code #.

The# value may be any ascii code 32 to 126.
Example:QUOTE=39 (39 is the single quote).

Note: Default value is 1. (allow quotes)


Special Key Combinations

CursorLeft Move left 1 cell
CursorRight Move right 1 cell

Note:-
The above keys change with the navigate option. If in edit mode, and the left/right cursor keys are not set to navigate, the cursor will move inside the cell being edited.

CursorUp Move up 1 cell
CursorDown Move down 1 cell

Ctrl + CursorUp Move selected row up.
Ctrl + CursorDown Move selected row down.

Home Jump to first column, current row.
End Jump to last column, current row.

Ctrl + Home Jump to first column, first row.
Ctrl + End Jump to last column, last row.

PageUp Jump up visible page (if 10 rows visible, jumps 10 rows)
PageDown Jump down visible page

Ctrl + PageUp Jump to top row, current column.
Ctrl + PageDown Jump to bottom row, current column.

Ctrl + H Hide current column.

Ctrl + F Find and replace in current column.

Ctrl + R Repeat value of cell above, paste into current cell.

Ctrl + + Add new row.

Ctrl + C Copy selected text to clipboard.
Ctrl + V Paste clipboard to current cell.
Ctrl + X Cut selected text and copy to clipboard.
Ctrl + Z Undo last action in current cell.

Ctrl + S Save current data file.

When Picklist is active, and an item is selected:-
Ctrl + K Add picklist item to current cell.
Ctrl + D Delete selected picklist item.

Esc Cancels edit mode.

Ctlr + Enter Start new line.
When entering data in the worksheet, you can start a new row before reaching the end of the current row, by pressing 'CTRL + ENTER'. This will return to the start position, on a new row. The default start position is column 1, but it may be set to any other column.

To set the start column.
When in edit, and in the column you wish to set as start, press 'CTRL + >'. This will set the current column as the start position. Now when you're entering data, you can press 'CTRL + ENTER', and you will be returned to this column, on a new row.

To cancel the start column press 'CTRL + <'. This will return the start column to 1.


Sorting

Standard Sorting
This allows multi column sorting of rows. Sorting is as simple as clicking on the column header.

GenScriber does not have the sorting problems associated with spreadsheets. It will never destroy your data. The rows cannot be broken by sorting, and it is easy to return to the original order.
To sort dates, see 'Special Sorting'.

Sorting is disabled by default.
To activate sorting, select 'Allow Sorting' in 'Tools>Preferences>Worksheet'

Sort by any column
To sort a column, click on a column title. Each click will toggle between ascending, decending, and then back to unsorted. A small arrow in the title will indicate this.

Sort by several columns
The multisort will allow you to sort by more than one column.

To multisort. First click a column header to sort it, then hold down the CTRL key and click on another column header.
To return all columns to unsorted, release the CTRL key and click a column title until the column as returned to unsorted.

Special Sorting
In addition to the standard sorting, there are a few special sorts. These are in the right-click and edit menu. The sort order is always ascending.

Sort as date
This will sort dates.
Because genscriber data is always text, and the dates you enter are always left the way you intended them, the date sort has to do something that no spreadsheet can do. It has to sort the dates as text without knowing which format you used.

Numerical date formats
USA and UK numerical formats cannot be mixed. There is no way for genscriber to know if 02/08/1580 is in february or august.

The UK date format can be selected in Tools>Preferences>Html Settings.

USA
8/23/1850
8-23-1850

OR
UK
23/8/1850
23-8-1850
8/1850

Mixed format
All of the formats below are automatically detected, and sorted. Even when each cell in the column has a different format.

Aug 23 1850
23 Aug 1850
23 August 1815
1850 aug 23
1850/08/23
Aug/1850
?/aug/1850
August
About Aug 1850
1850

Free text expressions like 'about mar 1850' or '1st qtr 1850' will be sorted into the correct year, and in some case the correct month, depending on the amount of actual date information provided. However, an expression like 'first qtr 185?' doesn't really stand a chance of being sorted as a date.


Sort backwards by word
This sorts backwards (right to left), starting with the word on the right of the data.
I find this useful for grouping locations and full names.
Example: 'Sheffield, Yorkshire, UK' would sort 'UK Yorkshire Sheffield' and 'Michael Hardy' would sort 'Hardy Michael'.
The sort order is always ascending.

Sort by last word
This sorts by just the last word in the cell data.

Example 'Michael John Hardy' would sort only on 'Hardy'. The sort order is always ascending.

Sort backwards by char
This sorts backwards (right to left) by character, starting with the character on the right of the data.
Can be useful in grouping data. The sort order is always ascending.


Special Copy

clipboard_24.jpg Special Copy: This will copy a single row of data to the clipboard and format it suitable for use in other applications.

Two formats are available. List and Tabulated

By default 'List' is used. To tabulate data, hold down CTRL key when clicking the 'Copy' button.

How to copy.
The data copied will be from the current cell to the end of the row.
Or from the start of the row to the current cell, if the SHIFT key is held down when clicking the 'Copy' button.

Example to copy as 'List'
1. place the cursor in the row you want to copy, in the column you want to start copying from.

2. Click the 'Copy' button.
The row is copied and formatted from the current column to the last column.

Example formatted as 'List'
FirstName: John
Surname: Hunt
BuriedBy: Joshua Brookes Chaplain
Relationship: son
MotherFirst: Mary
MotherSurname: Hunt
Source: LDS Film 2356998
Parish: Manchester
Church: Cathedral
Register: Burials 1792-1800

How to copy as 'Tabulated'
1. place the cursor in the row you want to copy, in the column you want to start copying from.
2. Hold down CTRL and Click the 'Copy' button.
The row is copied and formatted from the current column to the last column.

To copy 'Tabulated' and from first column to current column, Hold down CTRL and SHIFT when clicking the 'Copy' button.

Example formatted as 'Tabulated'
First,Surname,BuriedBy,Relationship,MotherFirst,MotherSurname
John,Hunt,Joshua Brookes Chaplain,son,Mary,Hunt


Special Paste

clippaste_24.jpgThe special paste will paste from the clipboard, either a single record formated as a list, or multiple records formatted as tabulated data.
The data can be taken from other applications (another instance of GenScriber, excel, openoffice etc.), or from websites that display formatted records in tables.

'Smart Paste' is on by default when pasting. This can be turned off in the Paste dialog if required.

When 'Smart Paste' is ON
The clipboard data must contain column titles in the first row.
Column order is not important. Column titles are compared to the existing worksheet, and any columns with matching titles will be pasted into the correct columns.
Any remaining columns that do not match will be discarded.

Example: If the worksheet columns are titled “Dog, Cat, Mouse” and the paste data titles are “Cat, Mouse, Elephant, Dog”, then Elephant will be discarded and Dog,Cat and Mouse will go into the correct columns.

Create New Document
There is an option in the Paste dialog to create a new document. The purpose of this is to allow you to start a new document with the correct column titles for the data you have in the clipboard.

When you select 'Create New Document' from the Paste dialogue, the dialog to create a new document will open, and any column titles found in the paste data will be entered for you.

When 'Smart Paste is OFF'
Data is pasted 'As Is'. Even if column titles do not match.
This is useful for cases where there are no column titles.
Be careful with this. It is quite easy to end up with data that is not relevent to the column title.

Example: If the worksheet columns are titled “Dog, Cat, Mouse” and the paste data titles are “Cat, Mouse”, then the 'Cat' data will go into the 'Dog' column, and 'Mouse' data will go into the 'Cat' column.