Need program/VBA to run that will help me extract portions of a string of text and put it in the next column

[Maggie Barr]

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:

[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]

GIVEN NAME	RETURNED NAME
Abies balsamea var. phanerolepis Fern.	Abies balsamea var. phanerolepis
Abutilon abutilon (L.) Rusby	Abutilon abutilon
Acer rubrum × A. saccharinum	Acer rubrum × A. saccharinum
Achillea millefolium ssp. borealis (Bong.)	Achillea millefolium ssp. borealis
Agrostis borealis Hartman ssp. americana (Hartman) Tzvelev	Agrostis borealis ssp. americana
Alisma plantago-aquatica L. var. americanum J.A. Schultes	Alisma plantago-aquatica var. americanum
Acnida tamariscina (Nutt.) Wood var. prostrata Uline & Bray	Acnida tamariscina var. prostrata
Asplenium trichomanes ssp. quadrivalens × Asplenium trichomanes ssp. trichomanes	Asplenium trichomanes ssp. quadrivalens × Asplenium trichomanes ssp. trichomanes
Aster ×blakei (Porter) House	Aster ×blakei
Carex viridula Michx. ssp. viridula var. viridula	Carex viridula ssp. viridula var. viridula
Salix eriocephala Michx. ssp. eriocephala var. eriocephala	Salix eriocephala ssp. eriocephala var. eriocephala
Solidago simplex Kunth. ssp. randii (Porter) Ringius var. monticola (Porter) Ringius	Solidago simplex ssp. randii var. monticola
Symphyotrichum lanceolatum (Willd.) Nesom ssp. lanceolatum var. latifolium (Semple & Chmielewski) Nesom	Symphyotrichum lanceolatum ssp. lanceolatum var. latifolium
Diphasiastrum alpinum (L.) Holub × D. complanatum	Diphasiastrum alpinum × D. complanatum
Echinochloa crus-galli var. frumentacea (Link) W. Wight	Echinochloa crus-galli var. frumentacea
Polygonum arifolium L. var. lentiforme Fern. & Grisc.	Polygonum arifolium var. lentiforme
Potamogeton perfoliatus × P. pusillus ssp. tenuissimus	Potamogeton perfoliatus × P. pusillus ssp. tenuissimus

<tbody>
</tbody>

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Maggie Barr]

Holy Comoley Batman!!!
Thanks, once again, MickG.
This is amazing, but there are a couple glitches that I would love it if we could get worked out. It appears that there is a trigger with some capital letters with a period to include the word after them, so in an authority like:
Arctostaphylos adenotricha (Fern. & J.F. Macbr.) A. & D. Löve & Kapoor
It returns:
Arctostaphylos adenotricha & Löve
The letters that appear most common to cause problems are A. and D., thought there are others like in row 4,427 in the Box net link file I have.
As well, in some of the hybrids where the × has a space on either side it is returning only one word after the ×. You see there are hybrids mentioned with the × without a space (attached to the species name), and then there are hybrids of two full species, so where the × has a space on both sides I need both the following words to be brought over.
So for instance:
Vaccinium angustifolium × V. corymbosum
Returns:
Vaccinium angustifolium × V.
But in the example:
Aster ×blakei (Porter) House
It reutrns:
Aster ×blakei like it should
It is probably that it may need to be split up as two functions one with the × without a space after it and one for the × with a space after it (to include two subsequent words).
As I said I have box net file where I scanned through and found some of the issues and highlighted them yellow in column B for you to see what I am talking about. Hopefully I can get the file to work for you this time.
https://app.box.com/s/tv2gtlfw0v2d9hpvyzhl
THANK YOU SOOOO MUCH!
Best,
Maggie

 

[Maggie Barr]

Thank you for your time WP, I do appreciate the effort, but my abilities with programming are just not there to have attempted what you were talking about. While I understood portions of what you were talking about, much of it was foreign to me.
Again,
Thank you for your time
Best,
Maggie

 

[MickG]

Try this , it should be better !!!

[COLOR="Navy"]Sub[/COLOR] MG10Sep11
[COLOR="Navy"]Dim[/COLOR] Rng             [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn              [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Txt             [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Str             [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n               [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] nn
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Txt = Split(Dn.Value, " ")
    Str = Str & Txt(0) & " " & Txt(1)
    [COLOR="Navy"]For[/COLOR] n = 2 To UBound(Txt)
        [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Txt(n)
            [COLOR="Navy"]Case[/COLOR] Chr(215)
             [COLOR="Navy"]For[/COLOR] nn = 2 To 4
                [COLOR="Navy"]If[/COLOR] nn <= UBound(Txt) [COLOR="Navy"]Then[/COLOR]
                    Str = Str & " " & Txt(nn)
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] nn
            [COLOR="Navy"]Case[/COLOR] Left(Txt(n), 2 = 1) = Chr(215): Str = Str & " " & Txt(n)
            [COLOR="Navy"]Case[/COLOR] "var.": Str = Str & " " & Txt(n) & " " & Txt(n + 1)
            [COLOR="Navy"]Case[/COLOR] "ssp.": Str = Str & " " & Txt(n) & " " & Txt(n + 1)
        [COLOR="Navy"]End[/COLOR] Select
    [COLOR="Navy"]Next[/COLOR] n
Dn.Offset(, 1).Value = Str: Str = ""
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Maggie Barr]

HEY MICK,
That was MUCH better. Please be patient with me...there is one more thing. With the hybrids, there appears to be an issue still with the output when there is authority names within the cell. Below are some of the items I am talking about. The first column is the original to modify, the second is the output from the new macro, and the third is the output from the original macro. I think it is still and issue with the × in the string. I had played with the program after responding and deleted the A. and D. part as you had, but it hadn't fixed everything. I also added another term 'forma' for species forms to search for (AND IT WORKED!). I can definitely work with this and just check for the errors, but do you have any ideas on what is going on and how it might get fixed?

Thank you, thank you,
Maggie

[TABLE="width: 1058"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Synonymy1[/TD]
[TD]NEW MACRO OUTPUT SYNONYMY1[/TD]
[TD]MACRO OUTPUT SYNONYMY1[/TD]
[/TR]
[TR]
[TD]Diphasiastrum alpinum (L.) Holub × D. complanatum[/TD]
[TD]Diphasiastrum alpinum (L.) Holub ×[/TD]
[TD]Diphasiastrum alpinum × D. complanatum[/TD]
[/TR]
[TR]
[TD]Fragaria chiloensis (L.) P. Mill. × F. virginiana[/TD]
[TD]Fragaria chiloensis (L.) P. Mill.[/TD]
[TD]Fragaria chiloensis × F.[/TD]
[/TR]
[TR]
[TD]Gymnocarpium dijunctum (Rupr.) Ching × G. dryopteris[/TD]
[TD]Gymnocarpium dijunctum (Rupr.) Ching ×[/TD]
[TD]Gymnocarpium dijunctum × G.[/TD]
[/TR]
[TR]
[TD]Leucanthemum lacustre (Brot.) Samp. × L. maximum (Ramond) DC.[/TD]
[TD]Leucanthemum lacustre (Brot.) Samp. ×[/TD]
[TD]Leucanthemum lacustre × L.[/TD]
[/TR]
[TR]
[TD]Diphasiastrum alpinum (L.) Holub × D. complanatum[/TD]
[TD]Diphasiastrum alpinum (L.) Holub ×[/TD]
[TD]Diphasiastrum alpinum × D. complanatum[/TD]
[/TR]
[TR]
[TD]Diphasiastrum alpinum (L.) Holub × D. complanatum[/TD]
[TD]Diphasiastrum alpinum (L.) Holub ×[/TD]
[TD]Diphasiastrum alpinum × D. complanatum[/TD]
[/TR]
[TR]
[TD]Salix babylonica L. × S. euxina[/TD]
[TD]Salix babylonica L. × S.[/TD]
[TD]Salix babylonica × S.[/TD]
[/TR]
[TR]
[TD]Viola sagittata var. ovata × V. sororia[/TD]
[TD]Viola sagittata var. ovata var. ovata ×[/TD]
[TD]Viola sagittata var. ovata × V.[/TD]
[/TR]
[TR]
[TD]Viola sagittata var. ovata × V. sororia[/TD]
[TD]Viola sagittata var. ovata var. ovata ×[/TD]
[TD]Viola sagittata var. ovata × V.[/TD]
[/TR]
</tbody>[/TABLE]

 

[WarPigl3t]

I tested this code and it works.
Here is the dataset that I used.
[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]GIVEN NAME
[/TD]
[TD]OUTPUT from code
[/TD]
[TD]What output should be[/TD]
[TD]Column B matches Column C?[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]Abies balsamea var. phanerolepis Fern.
[/TD]
[TD]Abies balsamea var. phanerolepis
[/TD]
[TD]Abies balsamea var. phanerolepis[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]Abutilon abutilon (L.) Rusby[/TD]
[TD]Abutilon abutilon[/TD]
[TD]Abutilon abutilon[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]Acer rubrum × A. saccharinum[/TD]
[TD]Acer rubrum × A. saccharinum[/TD]
[TD]Acer rubrum × A. saccharinum[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD]Achillea millefolium ssp. borealis (Bong.)[/TD]
[TD]Achillea millefolium ssp. borealis[/TD]
[TD]Achillea millefolium ssp. borealis[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD]Agrostis borealis Hartman ssp. americana (Hartman) Tzvelev[/TD]
[TD]Agrostis borealis ssp. americana[/TD]
[TD]Agrostis borealis ssp. americana[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD]Alisma plantago-aquatica L. var. americanum J.A. Schultes[/TD]
[TD]Alisma plantago-aquatica var. americanum[/TD]
[TD]Alisma plantago-aquatica var. americanum[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD]Acnida tamariscina (Nutt.) Wood var. prostrata Uline & Bray[/TD]
[TD]Acnida tamariscina var. prostrata[/TD]
[TD]Acnida tamariscina var. prostrata[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD]Asplenium trichomanes ssp. quadrivalens × Asplenium trichomanes ssp. trichomanes[/TD]
[TD]Asplenium trichomanes ssp. quadrivalens × Asplenium trichomanes ssp. trichomanes[/TD]
[TD]Asplenium trichomanes ssp. quadrivalens × Asplenium trichomanes ssp. trichomanes[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD]Aster ×blakei (Porter) House[/TD]
[TD]Aster ×blakei[/TD]
[TD]Aster ×blakei[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD]Carex viridula Michx. ssp. viridula var. viridula[/TD]
[TD]Carex viridula ssp. viridula var. viridula[/TD]
[TD]Carex viridula ssp. viridula var. viridula[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD]Salix eriocephala Michx. ssp. eriocephala var. eriocephala[/TD]
[TD]Salix eriocephala ssp. eriocephala var. eriocephala[/TD]
[TD]Salix eriocephala ssp. eriocephala var. eriocephala[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]13
[/TD]
[TD]Solidago simplex Kunth. ssp. randii (Porter) Ringius var. monticola (Porter) Ringius[/TD]
[TD]Solidago simplex ssp. randii var. monticola[/TD]
[TD]Solidago simplex ssp. randii var. monticola[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]14
[/TD]
[TD]Symphyotrichum lanceolatum (Willd.) Nesom ssp. lanceolatum var. latifolium (Semple & Chmielewski) Nesom[/TD]
[TD]Symphyotrichum lanceolatum ssp. lanceolatum var. latifolium[/TD]
[TD]Symphyotrichum lanceolatum ssp. lanceolatum var. latifolium[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]15
[/TD]
[TD]Diphasiastrum alpinum (L.) Holub × D. complanatum[/TD]
[TD]Diphasiastrum alpinum × D. complanatum[/TD]
[TD]Diphasiastrum alpinum × D. complanatum[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]16
[/TD]
[TD]Echinochloa crus-galli var. frumentacea (Link) W. Wight[/TD]
[TD]Echinochloa crus-galli var. frumentacea[/TD]
[TD]Echinochloa crus-galli var. frumentacea[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]17
[/TD]
[TD]Polygonum arifolium L. var. lentiforme Fern. & Grisc.[/TD]
[TD]Polygonum arifolium var. lentiforme[/TD]
[TD]Polygonum arifolium var. lentiforme[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]18
[/TD]
[TD]Potamogeton perfoliatus × P. pusillus ssp. tenuissimus[/TD]
[TD]Potamogeton perfoliatus × P. pusillus ssp. tenuissimus[/TD]
[TD]Potamogeton perfoliatus × P. pusillus ssp. tenuissimus[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
</tbody>[/TABLE]

Sub Macro1()
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    i = 2
    Do Until i > lastRow
        originalText = Range("A" & i).Value
        mySplit = Split(originalText, " ")
        mySplitMax = UBound(mySplit)
        outputString = mySplit(0)
        s = 1
        Do Until s > mySplitMax
            output = True
            openParenthesis = Left(mySplit(s), 1)
            If openParenthesis = "(" Then
                output = False
            End If
            If output = True Then
                capitalLetter = LCase(mySplit(s))
                If capitalLetter <> mySplit(s) Then
                    output = False
                End If
            End If
            If mySplit(s) = "&" Then
                output = False
            End If
            If output = True Then
                outputString = outputString & " " & mySplit(s)
            End If
            countLen = Len(mySplit(s))
            If countLen = 1 And mySplit(s) = "×" Then
                outputString = outputString & " " & mySplit(s + 1)
            End If
            s = s + 1
        Loop
        Range("B" & i).Value = outputString
        i = i + 1
    Loop
End Sub

 

[WarPigl3t]

All I did to come up with that code above was add End(xlup) to this line of code, "lastRow = Range("A" & Rows.Count).End(xlUp).Row" and add UBound to this line of code, "mySplitMax = UBound(mySplit)". Everything else was copy paste from the directions I gave you. I don't like giving people hand outs that they can just copy paste the code into their excel file and say it worked. The directions I gave you were sufficient enough for you to easily figure it out via google search. I hope you review my original post with the directions so you understand why this code works. This isn't a community of people that does things for you for free. We help each other to understand the problem, not do it for them.

 

[Maggie Barr]

Hello WarPiglet,
First, I want to thank you for your help. Second, I want to apologize for obviously offending you in some way, that would never have been an intention of mine in any way. I have always tried to be clear with my request for help, yet humble enough to point out the limitations of my skills within that request. I am sorry that what you had written to me had seemed a daunting list of things, as I have never written code before myself, and I am only now starting learning through this forum. I had received the code from Mick G at the same time as I did yours, and I am sorry if you were offended that I tried that first before going through the process you had presented. I apologize if people on here feel used in some way by others asking questions and for help. I have always expressed the utmost in gratitude for the help I have received, and as well, requested explanations of that help if they had time to do so thus enabling me to actually learn from the work others have put in to help me rather than grab a code and run. To be honest I am hurt by the tone in your note, and while the complex nature of the data set didn't make it an easy process, I felt that there were no rude requests for assistance on my part. I really do not know what else to say. I am sorry if there is something to be sorry for, but I currently can't see what I have done wrong to have received such a response.
Maggie

 

[Maggie Barr]

Well,
As it turns out Mick's macro produced less errors than the most recent contribution. There were too many pieces of authority names or notes that came through with that one, thus I will be using Mick's and simply hand proofing for the hybrids in the data set. I want to thank everyone for the effort in trying to help me develop a tool to use in my work. I am very grateful for the effort put forth to help me, and I apologize if the complex nature of the request took up too much of anyone's time.
Thanks you,
Maggie