Extracting multiple items from a cell before/after a certain character

[cloead1]

I'm trying to extract the 4 characters before and after the "-" in a cell. I have been successful in doing this for the first, last, and middle instance. However what I need to do is extract these characters for ALL instances and have them report to either a single cell or multiple cells. The end goal is finding the highest number value in the cell. If there's an easier way of doing it.. I'm all ears!

Here's a sample of a few cells:


[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 770"]
<colgroup><col width="770"></colgroup><tbody>[TR]
[TD="width: 770"]American Motors Eagle 1981-1980, Gremlin 1976-1975, Hornet 1976-1975, Matador 1978-1975, Pacer 1976-1975, International 100 1974, 150 1975, 200 1975-1974, Scout II 1980-1974, Travelall 1975, Jeep CJ5 1978-1974, CJ7 1978-1976[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 770"]
<colgroup><col width="770"></colgroup><tbody>[TR]
[TD="width: 770"]American Motors Concord 1983-1982, Eagle 1988-1982, Spirit 1983-1982, AM General Hummer 2001-1992, Hummer H1 2004-2002, H1 2006, Jeep Cherokee 1991-1984, CJ5 1983-1982, CJ7 1986-1982, Comanche 1992-1986, Scrambler 1985-1982, Wagoneer 1989-1984, Wrangler 1989-1987[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 770"]
<colgroup><col width="770"></colgroup><tbody>[TR]
[TD="width: 770"]American Motors Ambassador 1974-1971, Gremlin 1974-1970, Hornet 1974-1970, Javelin 1974-1971, Matador 1974-1971[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 770"]
<colgroup><col width="770"></colgroup><tbody>[TR]
[TD="width: 770"]American Motors AMX 1980-1978, Concord 1981-1978, Gremlin 1978-1977, Hornet 1977, Pacer 1980-1977, Spirit 1981-1979, Jeep CJ5 1981-1978, CJ7 1981-1978, Scrambler 1981[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 770"]
<colgroup><col width="770"></colgroup><tbody>[TR]
[TD="width: 770"]Ford F-100 1983-1980[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 770"]
<colgroup><col width="770"></colgroup><tbody>[TR]
[TD="width: 770"]Chrysler Dynasty 1993-1991, Imperial 1993-1991, New Yorker 1993-1991, Town & Country 1995-1991, Dodge Caravan 1995-1991, Dynasty 1993-1991, Grand Caravan 1995-1991, Monaco 1992-1991, Eagle Premier 1992-1991, Plymouth Grand Voyager 1995-1991, Voyager 1995-1991
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 770"]
<colgroup><col width="770"></colgroup><tbody>[TR]
[TD="width: 770"]Chrysler 300 1971-1969, Imperial 1973-1970, Newport 1973-1969, New Yorker 1973-1969, Town & Country 1973-1969, Dodge B100 Van 1972-1971, B200 Van 1972-1971, B300 Van 1972-1971, D100 Pickup 1972, D200 Pickup 1972, D300 Pickup 1972, Monaco 1973-1969, Polara 1973-1969, International 1010 1973-1972, Plymouth Fury 1973-1969, Fury I 1973-1969, Fury II 1973-1969, Fury III 1973-1969
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

So for example in the first row the end goal would be to have a cell that simply has 1981 in it. Second row, 2006, etc.

If anyone has any advice I would greatly appreciate it!

Thanks

 

[Rick Rothstein]

Yea I noticed any cell with models that included #'s weren't working right. It was only ~20 or so across the entire data set so I just handled those manually.

Yes, but are you sure you found and corrected all of them? That is suppose to be the point of having a macro... you should not have to check and/or correct anything manually after using it. Just out of curiosity... did you try the code I posted in Message #9 or was this a one-time only project that you will never have to do again?

 

[cloead1]

Yes, but are you sure you found and corrected all of them? That is suppose to be the point of having a macro... you should not have to check and/or correct anything manually after using it. Just out of curiosity... did you try the code I posted in Message #9 or was this a one-time only project that you will never have to do again?

I ended up writing a small program in c# that handled it after I made the thread :/ I'm much more well versed in that vs VB.

 

[Rick Rothstein]

I ended up writing a small program in c# that handled it after I made the thread :/ I'm much more well versed in that vs VB.

Then you should have posted that back to the thread at the time you did it so that others reading this thread would not waste their time working on a problem you no longer needed help with.

 

[cloead1]

Then you should have posted that back to the thread at the time you did it so that others reading this thread would not waste their time working on a problem you no longer needed help with.

If I had the option to delete a thread or edit a post I would of.

Sooorey you wasted your time. Please ban me immediately.

 

[Rick Rothstein]

If I had the option to delete a thread or edit a post I would of.

It would not have been necessary to delete the thread or edit a post... simply adding a new message to the thread saying you found/developed an alternative solution using C# would have been sufficient.

Sooorey you wasted your time. Please ban me immediately.

It is not so much I wasted "my" time, rather, it is time I (and maybe others who tried but didn't post) could have spent helping others who came to this forum, like you did originally, looking for help. The time I (we) spent working on your problem which you no longer needed a solution for could have been used helping them instead.

 

[cloead1]

It would not have been necessary to delete the thread or edit a post... simply adding a new message to the thread saying you found/developed an alternative solution using C# would have been sufficient.



It is not so much I wasted "my" time, rather, it is time I (and maybe others who tried but didn't post) could have spent helping others who came to this forum, like you did originally, looking for help. The time I (we) spent working on your problem which you no longer needed a solution for could have been used helping them instead.

The problem no longer needed a solution after Fennek posted one as displayed by my "Thanks!" post. Any work done after that on your end or anyone else's is on them.

 

[Rick Rothstein]

The problem no longer needed a solution after Fennek posted one as displayed by my "Thanks!" post. Any work done after that on your end or anyone else's is on them.

If all you said was "Thanks", then I might agree with you, but you didn't, you also said "Works perfectly!!!". The fact that the code did not work perfectly invited the correction as you may not have realized you were using code that produced erroneous results. The problem is that you actually did not care if it was producing correct results or not because you decided to use another solution. All I am saying is that since you physically started the thread, common courtesy would have been to tell us you no longer were interested in a solution for it.