Spreadsheet tips for taxonomists

A quick reference for Taxonomic Database editing in Excel
N. Hensold, December 2012

This is a crib sheet useful for common problems in taxonomic database editing. For example -- joining genus and species from two columns into one -- or separating them again. It also shows how to get a unique species list from a specimen database. Lately I'm trying to cobble together a species list for northwestern Peru, using data from three sources, and thought it might be useful to summarize the procedure in a place I can find it again. These are my personal notes, and they work on my machines.
Note: This is a quick reference, and not intended as a beginner's guide to Excel. ... Use at your own risk!

General reminders on use of formulas:
   Enter a formula into the cell where you want to see the result.
   Start a formula with = sign.
   Write the formula in the top row to apply to cells of that row.
     Then you can drag it down to propagate it for all rows.
   Remember if you have a header the top row of data is '2'
   After you perform the operation on the whole column, copy the column, and do a "Paste Special" -- "Values Only" -- to get rid of the formulas.

FUNCTION SYNTAX

=FIND("text",cell#,starting position in cell)
-----> returns numeric value
[NOTE: New Excel may use SEARCH fxn instead of FIND]

=REPLACE(old text,start_num,num_chars,new_text)

=CONCATENATE (text1&text2&text3....)

=LEN(A2)
= number of characters in Cell A2

FUNCTION EXAMPLES
(cell locations 'A2,' 'B2' etc. given below are used as examples only)

To find a space between words:
=FIND(" ",A2,1)
Example: Inga pavoniana -- returns value "5"

To find a second space between words in the same cell:
=FIND(" ",A2,FIND(" ",A2,1)+1)
in other words,
find a "space" in Cell A2, starting at the letter just after the first space you found.

TO SPLIT DATA FROM 1 COLUMN INTO 2 or MORE

To Delete everything after the first space:
=REPLACE(B2,FIND(" ",B2,1),LEN(B2)-FIND(" ",B2,1)+1,"")

To Delete everything after the second space:
=REPLACE(B2,FIND(" ",B2,FIND(" ",B2,1)+1),LEN(B2) - FIND(" ",B2,FIND(" ",B2,1)-1),"")

To Delete everything before the first space:
=REPLACE(B2,1,FIND(" ",B2,1),"")

To Delete everything before the second space:
=REPLACE(B2,1,FIND(" ",B2,FIND(" ",B2,1)+1),"")

TO JOIN DATA FROM 2 COLUMNS INTO 1 (with space between)

Create an empty column. In top cell:
=CONCATENATE(B2&" "&C2)

TO CREATE A UNIQUE LIST
For example, from a specimen database with multiple entries of the same name, make a list of unique name occurrences.

Pick a column a couple cells to the right of the main spreadsheet. Make a note of the Column letter, e.g. "Z"

Go to Data -> Filter -> Advanced Filter
Select top cell in the Column you want to create the list from.
In the pop-up box,
   *Click: "Copy to another location"
   List Range (for Column B, for example):
        $B:$B
   Copy to:
        $Z$1
   *Click:   "Unique Records Only"