Detecting year ranges seperated by a dash?

[spyrule]

Hello,
I've got an excel sheet with ~120K rows of text. The text contains automotive data, and it is NOT consistent in location/placement/formatting, except the year ranges it seems.

Sample data:

'02-06 Acura RSX Base/ 02-06 Honda Civic Si 4-2-1 Polished S.S. Header One Piece'
'96-97 Acura Integra Type-R 4-1 Polished Stainless Steel Race Header One Piece'
'Brake Pads [Mercedes-Benz 240d(1974-82), Mercedes-Benz 230(1977-1978), Volvo 164(1972-1975), Volvo 145(1971-1974), Porsche 914(1970-1975), Porsche 911(1970-1974, 1978)]'

I need help writing a formula that checks for any number from (19)80 through (20)18 BEFORE a dash, and again after the dash. If that pattern exists, simply put what that pattern is.

So in each case, the first row would return "02-06" and the second row would return "96-97" and the third example would return nothing (we don't care about products pre-1980).

Any suggestions/help is appreciated. If I HAVE to go to a VBA function I'm ok with that, as long as I can pass through the source cell text.

 

[jarjarbingie]

Hello,
I've got an excel sheet with ~120K rows of text. The text contains automotive data, and it is NOT consistent in location/placement/formatting, except the year ranges it seems.

Sample data:

'02-06 Acura RSX Base/ 02-06 Honda Civic Si 4-2-1 Polished S.S. Header One Piece'
'96-97 Acura Integra Type-R 4-1 Polished Stainless Steel Race Header One Piece'
'Brake Pads [Mercedes-Benz 240d(1974-82), Mercedes-Benz 230(1977-1978), Volvo 164(1972-1975), Volvo 145(1971-1974), Porsche 914(1970-1975), Porsche 911(1970-1974, 1978)]'

I need help writing a formula that checks for any number from (19)80 through (20)18 BEFORE a dash, and again after the dash. If that pattern exists, simply put what that pattern is.

So in each case, the first row would return "02-06" and the second row would return "96-97" and the third example would return nothing (we don't care about products pre-1980).

Any suggestions/help is appreciated. If I HAVE to go to a VBA function I'm ok with that, as long as I can pass through the source cell text.

try this:

=IF(AND(ISNUMBER(MID(A2,SEARCH("-",A2)-2,2)*1),ISNUMBER(MID(A2,SEARCH("-",A2)+1,2)*1)),MID(A2,SEARCH("-",A2)-2,5))

 

[spyrule]

try this:

=IF(AND(ISNUMBER(MID(A2,SEARCH("-",A2)-2,2)*1),ISNUMBER(MID(A2,SEARCH("-",A2)+1,2)*1)),MID(A2,SEARCH("-",A2)-2,5))

Yup that worked. I only had to add a value if false option for when no date ranges are found, and make the source text cell sticky (simply added the $).

Here is the final formula in case anybody else ever needs it:

[COLOR=#333333]=IF(AND(ISNUMBER(MID($A2,SEARCH("-",$A2)-2,2)*1),ISNUMBER(MID($A2,SEARCH("-",$A2)+1,2)*1)),MID($A2,SEARCH("-",$A2)-2,5),"")[/COLOR]

 

[ukmikeb]

Hi

In your OP you said :-

Hello,
I need help writing a formula that checks for any number from (19)80 through (20)18 BEFORE a dash, and again after the dash. If that pattern exists, simply put what that pattern is.

So in each case, the first row would return "02-06" and the second row would return "96-97" and the third example would return nothing (we don't care about products pre-1980).

Yup that worked. I only had to add a value if false option for when no date ranges are found, and make the source text cell sticky (simply added the $).

Here is the final formula in case anybody else ever needs it:

[COLOR=#333333]=IF(AND(ISNUMBER(MID($A2,SEARCH("-",$A2)-2,2)*1),ISNUMBER(MID($A2,SEARCH("-",$A2)+1,2)*1)),MID($A2,SEARCH("-",$A2)-2,5),"")[/COLOR]

Are you really sure that your final formula complies?
See what follows :-
spyrule

	A	B	C
2	'02-06 Acura RSX Base/ 02-06 Honda Civic Si 4-2-1 Polished S.S. Header One Piece'	02-06	02-06
3	'96-97 Acura Integra Type-R 4-1 Polished Stainless Steel Race Header One Piece'	96-97	96-97
4	'Brake Pads [Mercedes-Benz 240d(1974-82), Mercedes-Benz 230(1977-1978), Volvo 164(1972-1975), Volvo 145(1971-1974), Porsche 914(1970-1975), Porsche 911(1970-1974, 1978)]'
5	79-80 Acura RSX Base/ 02-06 Honda Civic Si 4-2-1 Polished S.S. Header One Piece'	79-80
6	19-20 Acura RSX Base/ 02-06 Honda Civic Si 4-2-1 Polished S.S. Header One Piece'	19-20
7	Brake Pads [Ford(1974-82), Ford(1977-1978), Volvo 164(1972-1975), Volvo 145(1971-1974), Porsche 914(1970-1975), Porsche 911(1970-1974, 1978)]'	74-82

<thead>
</thead><tbody>
</tbody>

Excel 2007

Worksheet Formulas Cell Formula B2 =IF(AND(ISNUMBER(MID(A2,SEARCH("-",A2)-2,2)*1),ISNUMBER(MID(A2,SEARCH("-",A2)+1,2)*1)),MID(A2,SEARCH("-",A2)-2,5),"") C2 =IFERROR(IF(AND(ISNUMBER(MID(A2,SEARCH("-",A2)-2,2)+0),ISNUMBER(MID(A2,SEARCH("-",A2)+1,2)+0)),IF(AND(OR(MID(A2,SEARCH("-",A2)-2,2)+0>79,MID(A2,SEARCH("-",A2)-2,2)+0<19),OR(MID(A2,SEARCH("-",A2)+1,2)+0>79,MID(A2,SEARCH("-",A2)+1,2)+0<19)),MID(A2,SEARCH("-",A2)-2,5),""),""),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Fyi the reason that row 4 results in a blank is not for the incorrect year but for the "s-" in Mercedes-Benz.
My formula also fails for the same reason.

hth