Thursday, September 22, 2011

Part 1: Copy and paste plant symbol codes into Colin's Plant Code Lookup Excel spreadsheet tool to convert them into scientific/latin names, common names, and families.

Edit 4/25/2013:  I updated all spreadsheets to be able to convert Scientific Names to Accepted Plant Symbols, which was a requested feature.  In order to do this, I had to eliminate plant synonyms from the data list. But, I added a few other columns such as Family Common Name, Family Symbol, and Order.  The images below look slightly different than those in the updated spreadsheets.  - Colin

After you download the appropriate plant code converter spreadsheet that you need (described in the Introduction to Plant Code Lookup and Replacement page and depends on if you have Excel 2003 or 2007/2010 and if you wish to have the macro features or not), you'll find the following tabs along the bottom of the worksheet (Fig. 1):

Figure 1. Click on image to enlarge. Some versions of this plant workbook may not have all the tabs shown.

1. Click on the "Symbol Lookup" tab (3rd tab from left).  The page should look like this (Fig. 2):

Figure 2.  Example of pasting plant symbol codes into the yellow cells of the "Symbol Lookup" tab to convert them into scientific names, common names, family names, etc.
2. Enter your 4 letter codes into the yellow cells on the left.

The first six yellow cells have example data entered into them and these can be deleted or replaced.  You can copy/paste long lists from another worksheet or enter them individually.  It doesn't matter if you use Uppercase or Lowercase characters.  Codes must be entered exactly as they are listed in the plants database or you'll get a "N/A" error or you might lookup the wrong plant.

3. Copy and Paste the plants common names, family names, or latin names back to your working document.  If pasting into another Excel worksheet, be sure to use "Paste Values" (shortcut keys "alt," "e," "s," and "v").

Custom Code Lookup/Replacement

If you choose to NOT use USDA plant codes in favor of your own list of custom codes, use the 3rd and 4th tabs.  First, enter your custom symbol codes and names on the "LIST2" tab, then use the "Custom Name Lookup" tab for all future lookups or code replacements.

  • The spreadsheed only allows for 200 codes to be entered.  More lines can easily be added by copying   and pasting additional rows.
  • To edit the "Symbol Lookup" worksheet, you have to unprotect it first.  In Excel 2007/2010, this under on the "Review" menu.
  • One useful way to use this tool would be to do your data analysis, summaries, or pivot tables using the codes and for the final table, copy and paste the list of plant codes into the Symbol Lookup worksheet to get the common names
  • To paste the scientific names or other data into another spreadsheet, you have to use "paste values":
    1. Highlight and copy the information (scientific names, common names, family names, etc) you want. 
    2. On your new spreadsheet, select the cell where you want to paste the info 
    3. Under the paste menu, select "paste special", and then select "values".
    • Or, do the following shortcut keys: 
      •  i. hit the "alt" key 
      •  ii. hit the "e" key 
      •  iii. hit the "s" key 
      •  iv. hit the "v" key
  • The Excel 2007/2010 versions of this spreadsheet (has a .xlsx or .xlsm extensions) have all plant species in the list from the USDA Plants Database (downloaded from the USDA Plants Database on June 17, 2011)  
Move to the Next Section (Part 2):