Part 3: Updating the Plant Look-Up Spreadsheet Lists from the USDA Plants Website

UPDATING THE DATABASE LOOK-UP LIST IN THE PLANT SPREADSHEETS

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

If you're using Excel 2003, you should download the ".xls" files.   If you're using Excel 2003, be aware that there is a 65,536 row data limit. If you use the current list without plant synonyms, it's not a problem. However, if you wish to include plant synonyms, you may have to limit your list to a particular state.  To download another state (or to update the LIST worksheet in any of the workbooks), follow these instructions:

1. You'll need to download the list of plants from the USDA PLANTS database:
• Click here to open the link to the plants database with the correct settings selected.
• Click on "Review Selections or Sort Report"
• Click on "Modify Selections"
• Select the state you want plant info for or select other options to display.
• Click on "display results" at bottom of the screen. 
• On the new screen that comes up, click on the word "download" in small print on the upper right-hand portion of the screen, next to "Printer Friendly" 
• A new screen will appear with comma-delimited text. 
• Save the webpage as a text file.  There are many ways to do this, one way is to copy all the text, open notepad, paste the text, and save the file as a ".txt". 
2. Import plant list into Excel 
• Open Excel
• Click on "File" and "Open"
• Be sure the type of files being displayed is "txt" or "all files"
• On the first, "Step 1", screen be sure the circle or box next to "Delimited" is marked. 
• On the second step scree, be sure there's a check mark next to "comma" and the others are unchecked.
• On the final screen, click on "finish".
3. Copy all the plant codes and other data into this spreadsheet's "LIST" tab.
• On the plant list you imported into Excel in the last step, select all cells (or press  "Ctrl + a"), then press the copy button (or "Ctrl + c").  This will highlight and copy the entire worksheet.
• On the "LIST" tab of this workbook, press "Ctrl + a" and then either press the paste button or "Ctrl +v".
• Re-save the "Plant Code Lookup" file with the new state name abbreviation at the end.

You're ready to go!

Note: using the Excel 2003 version, plant families are not downloaded so will not show up on the code lookup page.


Review other sections of this topic:

• Introduction: Replace Plant Symbol Codes with Common Names, Latin (Scientific) Names, or Family Names (with and without Excel macros).
• 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.
• Part 2:  Use Colin's Plant Code Lookup Excel macro to convert plant symbol codes to scientific/latin names, common names, and family names.
• Part 3:  Customizing Colin's Plant Code Lookup Excel spreadsheet with individual states or custom lists.

===================================================================
Also, see my blog and free download to convert AOU bird species codes into common name, scientific name, or family name.  The tool also helps sort bird lists in phylogenetic order.

Part 2: Using the USDA Plant Code Lookup Excel Macro

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

If you downloaded the version of the plant code replacement spreadsheet with macros, you can use shortcut keys to convert between USDA plant codes, common names, latin names, etc.

To see if you have the version with macros, go to the "View" tab of the ribbon and click on "Macros". If "plant_list" macros show up, then you have macros.

The most useful use of this worksheet with macros will be to convert plant symbols in your datasheets with common names, family names, or latin names.  To do this, do the following:

1. Have the macro-enabled "Plant Code Lookup" file open at the same time your excel worksheet with plant data is open. (Be sure to enable macro content if your security settings are medium or high).
2. Highlight the columns or cells with plant symbol codes in it.
3. Press one of the shortcut keys to replace the code symbol with another name, as identified below: 

• Ctrl+Shift+C = replace code with common name 
• Ctrl+Shift+F = replace code with family name 
• Ctrl+Shift+L = replace code with latin name  

Another use of the macros is to replace latin names with common names or replace common names with latin names. There are limitations to the use of these commands as noted below:

• Ctrl+Shift+O = replace latin with common name.  NOTE:  The entire latin name needs to be written exactly the same as in the "LIST" tab, including the variety and "authority" name.  Suggest you customize the "LIST" tab to your needs.
• Ctrl+Shift+A = replace common name with latin.  BEWARE: there are common names cross-reference with multiple latin names.  Suggest you customize the "LIST" tab to your needs.

Use the "Macro Test" tab of this worksheet to test how this function works before you do it on your personal data.  You cannot undo changes made by a macro command!

Another note:  If you have another workbook with macros in it that use the same shortcut keys that are used in this macro workbook, pressing the shortcut keys run the other macro.

In this example the rose color columns (A through D) are originally entered  plant species data.  Greenish columns (F thru J) are data altered with macros and ready for pivot tables or sorting. The macro to automatically fill-in the blank cells in columns F and G is found in the "BirdMacroLibrary.xlsm" spreadsheet.

Move to the Next Section (Part 3):

• Introduction: Replace Plant Symbol Codes with Common Names, Latin (Scientific) Names, or Family Names (with and without Excel macros).
• 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.
• Part 2:  Use Colin's Plant Code Lookup Excel macro to convert plant symbol codes to scientific/latin names, common names, and family names.
• Part 3:  Customizing Colin's Plant Code Lookup Excel spreadsheet with individual states or custom lists.