Wetland Delineation Plant Name Look-up Tool

This tool is similar to my "Bird Macro Library" and "Plant Code Lookup" Excel tools that convert AOU bird codes or standardized plant symbol codes into scientific names, common names, family names, etc.

This Excel tool takes your list of plant symbols or latin names, compares them to the list of codes or names in the National Wetland Plant List (2012), and returns the Wetland Indicator Status for the region that you select.  For instance, it'll return "OBL", "FAC", "FACU", "UPL" if the plant is listed in the database.

The macro-enabled spreadsheet (Excel 2007 and 2010 only for now) can be downloaded here:
http://fielddata.blogspot.com/p/downloads.html

Data for the lists comes from the 2012 National Wetlands Plant List as found on the USDA PLANTS website as of April 8, 2012 at:  http://plants.usda.gov/wetland.html

Thanks to the USDA NRCS, ACOE, USFWS, and other other organizations and individuals who contributed to this list.

There are two ways to convert the data, one way is copying and pasting your information into the spreadsheet tables and the other uses VBA Macros to lookup and convert data in your existing spreadsheets to show the Wetland Indicator Status.  Here are some basic instructions:

Instructions (Non-Macro):

1. Download and open the file "NationalWetlandPlantListMacros.xlsm". Use the worksheet tabs "Code Lookup," "Latin Name Lookup," or "Common Name Lookup."
2. Copy your plant codes or latin names into the columns highlighted in yellow.
• Note: This method supports plant lookups on the subregion list, the macro method does not at this time.
3. Example data are in the first columns and can be replaced.  Insert rows as necessary.
4. Sheets are protected to prevent change to the non-highlighted cells.  Use Unprotect Sheet on the "Review" tab to undo.
5. If using common names, note that not all plants have common names in the wetland plant list. Try converting to scientific names or symbol using the "Plant Code Lookup" spreadsheet first.

Instructions (Macro):

1. Download, open, and enable macros in the file "NationalWetlandPlantListMacros.xlsm".
2. While the macro library is open, the macros can be run in any other workbook that is also open on your computer.
3. In your spreadsheet, make a copy of the column with the plant code symbols or latin names that you want to look up.
• For instance:  If your names are in column A, make a copy of the names in column B.
• This is because codes or names will be overwritten and cannot be undone.
4. Highlight the codes that you want to convert (i.e. the ones in column B) and press the shortcut key:
• Ctrl+w to convert plant symbol codes into Wetland Indicator Status Codes.  A selection box (see below) will ask which region you want to convert to.
• Ctrl+Shift+W to convert latin names into Wetland Indicator Status Codes.  Again, a selection box will ask which region you want to convert to.
• Note: plant latin names must be spelled exactly the same as in the "NWPL" tab.

Tip:  Practice the macros on the "Macro Test" tab to get a good idea of how the tools work. Also, backup your work before using the tool until you are accustomed to using it.  Note that if you change the name of the workbook or the worksheet tabs, some of the formulas and macros won't work.

If you wish, learn about using VBA Macros for Wetland, Wildlife, and other Natural Resource projects here.

As this is a new tool at this time, there may be some errors, so be sure to review the output and let me know if you come across any problems or if you have any questions and please sign my guestbook or send me a note to let me know how you find any of this information useful!

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.

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.

Notes:

• 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):

• 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.

Introduction: Replace USDA Plant Symbol Codes with Common Names, Latin (Scientific) Names, or Family Names (with and without Excel macros).

Introduction:

Here's a tool that I created for research and monitoring data that botanists or other outdoor professionals may find useful.  It's often convenient to use codes for recording field data because it takes up less space on your datasheet and less time.  Codes are also faster to enter into the computer when data entry time comes around.  However, if you use MS Excel to enter data with and then wish to produce a table for a report, presentation, or publication, you'll have to convert those codes into common names and/or latin names to make them more readable.

A common code system for recording plant names in the United States is used in the USDA NRCS PLANTS Database.  Plant species "symbols," as they're called, are comprised of the first two letters of the genus, followed by the first two letters of the species, the first single letter of the variety name (if present), and sometimes a tie-breaking number*.  Many vegetation monitoring projects that I've been involved with use these symbols to record species names in the field and in databases.

I developed Excel spreadsheets to convert these plant symbols into common names, Latin (scientific) names, and plant taxonomic family names to help in the reporting and organization of plant data.  You can convert lists of symbols by copying and pasting your data into my downloadable worksheets, or use a macro to convert symbols directly on your own Excel worksheet.

Use the following links to learn how to use my spreadsheet tools, or read the section below to learn about how to download the spreadsheets and which version you want.

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.


Where and What to Download:

On my downloads page, you'll find four versions of the Plant Code Lookup spreadsheets.  To decide which version you want or need depends on which version of Excel you have (2003 or 2007/2010), and whether you trust downloading Excel spreadsheets with macros or not.  You can read about macros by clicking here, but in short: My macro spreadsheets offer nice features that the non-macro versions don't, but some folks don't trust downloading spreadsheets with macros because they can potentially carry viruses with them.  I can give you my word that the spreadsheets were virus-free at the time that I uploaded them and have taken modest precautions to keep the files safe, but downloading and use of these spreadsheets is otherwise your choice and it's your responsibility to keep your computer safe by maintaining anti-virus software, etc.  You can read my software disclaimer on this website, contact me and ask me to email you the file directly, or download a non-macro version of my spreadsheet.  Here's a description of the four versions (all are free):

• Plant Code Lookup - all plants -with Macros.xlsm - Excel 2007 or 2010. Contains the Macros.
• Plant Code Lookup - all plants - No Macros.xlsx - Excel 2007 or 2010. No Macros.
• Plant Code Lookup - all plants - with Macros.xls - Excel 2003, 2007, or 2010. Contains Macros.
• Plant Code Lookup - all plants - No Macros.xls - Excel 2003, 2007, or 2010. No Macros.

The Excel 2007/2010 worksheets are labeled with "all plants" because they contain all the plant codes in the US that were in the PLANTS database as of August 2011.  The 2003 version doesn't allow that many lines of data, so only one US state's plant list, New Mexico, is included.  All of the spreadsheet include all plants in the USDA PLANTS database, but do not include plant synonyms.  I needed to eliminate synonyms in order to add the ability to convert Scientific names to symbol codes, which was a requested feature.

Click on the button below to access the downloads page for spreadsheet tools, Excel macro libraries, etc. (or get general info about my natural resources downloads here). 

Example of Use:

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.

====> Go to Next Section (click here).

* USDA, NRCS. 2011. The PLANTS Database (http://plants.usda.gov, 11 August 2011). National Plant Data Team, Greensboro, NC 27401-4901 USA.

===================================================================
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.