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

 

[Fennek]

Hi,

just an idea:

dim LatestDate as integer
Tx = split(cells(1,1), "-")
for j = 0 to ubound(Tx) step 2
LatestDate = iif(--right(Tx(j),4) > LatestDate,--right(Tx(j),4), LatestDate) 
next

untested, so pls debug it

regards

 

[cloead1]

Thanks! But it errored, see below


-- removed inline image ---


Doesn't like the LatestDate = line

 

[cloead1]

I guess I can't paste a screen shot into a post lol

 

[Fennek]

break in line 3?

of course! The code is just a the core and you have to add a few lines.

Please have a coffee-break and then try to understand the code.

 

[cloead1]

haha unfortunately my VB skills are extremely lacking

 

[Fennek]

Hi,

it was a bit more complicated than I expected:

Sub Fen()
'Texts in column A, Column B is empty
Dim LatestDate As Integer
lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lr
    If InStr(1, Cells(i, "A"), "-") > 0 Then
        Tx = Split(Cells(i, 1), "-")
        For j = 0 To UBound(Tx) Step 2
            If Right(Tx(j), 4) Like "####" Then
                LatestDate = IIf(--Right(Tx(j), 4) > LatestDate, --Right(Tx(j), 4), LatestDate)
            End If
        Next j
        Ty = Split(Cells(i, "A"), ",")
        For Each T In Ty
            If Right(T, 5) Like " ####" Then
                LatestDate = IIf(--Right(T, 4) > LatestDate, --Right(T, 4), LatestDate)
            End If
        Next T
        Cells(i, "B") = LatestDate
        LatestDate = 0
    End If
Next i
End Sub

regards

 

[cloead1]

Hi,

it was a bit more complicated than I expected:

Sub Fen()
'Texts in column A, Column B is empty
Dim LatestDate As Integer
lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lr
    If InStr(1, Cells(i, "A"), "-") > 0 Then
        Tx = Split(Cells(i, 1), "-")
        For j = 0 To UBound(Tx) Step 2
            If Right(Tx(j), 4) Like "####" Then
                LatestDate = IIf(--Right(Tx(j), 4) > LatestDate, --Right(Tx(j), 4), LatestDate)
            End If
        Next j
        Ty = Split(Cells(i, "A"), ",")
        For Each T In Ty
            If Right(T, 5) Like " ####" Then
                LatestDate = IIf(--Right(T, 4) > LatestDate, --Right(T, 4), LatestDate)
            End If
        Next T
        Cells(i, "B") = LatestDate
        LatestDate = 0
    End If
Next i
End Sub

regards

Works perfectly!!! Thanks so much!

 

[Rick Rothstein]

Works perfectly!!! Thanks so much!

It appears to me that Fennek's code returned the wrong year for this example that you posted...

Ford F-100 1983-1980

Here is another (more compact) macro for you to consider... it works correctly for all of the examples that you posted...

[table="width: 500"]
[tr]
	[td]Sub MaxCarYear()
  Dim X As Long, YearNum As Long, MaxYear As Long, Cell As Range, Comma() As String
  For Each Cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
    Comma = Split(Cell, ",")
    MaxYear = 0
    For X = 0 To UBound(Comma)
      YearNum = Val(Left(Right(Comma(X), 4 - 5 * (Mid(Comma(X), Len(Comma(X)) - 4, 1) = "-")), 4))
      If YearNum > CLng(MaxYear) Then MaxYear = YearNum
    Next
    If MaxYear > 999 Then Cell.Offset(, 1) = MaxYear
  Next
End Sub[/td]
[/tr]
[/table]

 

[cloead1]

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.

Thanks again!