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. Better yet - activate Fill Handle & double-click on it.
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.
=FIND("text",cell#,starting position in cell)
-----> returns numeric value
[NOTE: New Excel may use SEARCH fxn instead of FIND]
= number of characters in Cell A2
(cell locations 'A2,' 'B2' etc. given below are used as examples only)
To find a space between words:
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:
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:
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.
UPDATE: Aha! (New version Excel)
Copy columns in question to another sheet. Highlight them. Click "Remove Duplicates"
OLD INSTRUCTIONS (Old version Excel)
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):
*Click: "Unique Records Only"
TO COMPARE CONTENTS OF TWO LISTS
Highlights items which appear in both lists, or in only one list.
1. Select both lists (columns).
2. Click 'Conditional Formatting' --> Highlight Cells Rules --> Duplicate Values [or Unique]