Maggie Barr
Board Regular
- Joined
- Jan 28, 2014
- Messages
- 188
Hello There,
I am in need of a program/VBA to run that will help me extract portions of a string of text and put it in the next column. I regularly have to compare species lists. Plant species, beetles, butterflies etc., and I usually get these lists in a format that contains the authority of the species within the name, so I have, in the past, had to use text to columns and manually reorganize the data so that the authorities are removed in order to use VLOOKUP to merge databases based on the name as the authorities are not always entered the same way. This can take a lot of time. I am in hopes that someone can help me with the task. I am using a PC with Excel 2010.
Below is a sample list of species and how they are often submitted (Given Name) and how I need the format (Returned Name). What I need the code to do is take the first two words in the cell (genus species, space delimited), and then search the rest of the cell for ‘var.’, ‘ssp.’, or both, and return those terms WITH the word following those terms. Sometimes there will be a variety with a subspecies and sometimes a subspecies with a variety; therefore, some species/cells will have both of those terms and need both brought over with the name/word following the term in the correct order. As well there can be hybrids that are reported with × (this is not a normal x). These could be attached to the species name (the second name with no space), or they could be two genus species names with × in between them spaced accordingly. If the × is spaced, I need the two words following it returned with the ×. Note with hybrids there are not always authorities to worry about but sometimes.
This is a complex thing for me to explain, and I hope I have made it relatively clear, but do not hesitate to ask questions.
Thank you so much, in advance, for taking the time to consider the puzzle I have put forth. I am open to any advice.
Best,
Maggie
Let me illustrate:
<tbody>
</tbody>
I am in need of a program/VBA to run that will help me extract portions of a string of text and put it in the next column. I regularly have to compare species lists. Plant species, beetles, butterflies etc., and I usually get these lists in a format that contains the authority of the species within the name, so I have, in the past, had to use text to columns and manually reorganize the data so that the authorities are removed in order to use VLOOKUP to merge databases based on the name as the authorities are not always entered the same way. This can take a lot of time. I am in hopes that someone can help me with the task. I am using a PC with Excel 2010.
Below is a sample list of species and how they are often submitted (Given Name) and how I need the format (Returned Name). What I need the code to do is take the first two words in the cell (genus species, space delimited), and then search the rest of the cell for ‘var.’, ‘ssp.’, or both, and return those terms WITH the word following those terms. Sometimes there will be a variety with a subspecies and sometimes a subspecies with a variety; therefore, some species/cells will have both of those terms and need both brought over with the name/word following the term in the correct order. As well there can be hybrids that are reported with × (this is not a normal x). These could be attached to the species name (the second name with no space), or they could be two genus species names with × in between them spaced accordingly. If the × is spaced, I need the two words following it returned with the ×. Note with hybrids there are not always authorities to worry about but sometimes.
This is a complex thing for me to explain, and I hope I have made it relatively clear, but do not hesitate to ask questions.
Thank you so much, in advance, for taking the time to consider the puzzle I have put forth. I am open to any advice.
Best,
Maggie
Let me illustrate:
<tbody> </tbody> |
<tbody>
</tbody>
Last edited: