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[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[/TR]
</thead><tbody>[TR]
[TH]2[/TH]
[TD]'02-06 Acura RSX Base/ 02-06 Honda Civic Si 4-2-1 Polished S.S. Header One Piece'[/TD]
[TD]02-06[/TD]
[TD]02-06[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]'96-97 Acura Integra Type-R 4-1 Polished Stainless Steel Race Header One Piece'[/TD]
[TD]96-97[/TD]
[TD]96-97[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]'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)]'[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]79-80 Acura RSX Base/ 02-06 Honda Civic Si 4-2-1 Polished S.S. Header One Piece'[/TD]
[TD]79-80[/TD]
[TD][/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD]19-20 Acura RSX Base/ 02-06 Honda Civic Si 4-2-1 Polished S.S. Header One Piece'[/TD]
[TD]19-20[/TD]
[TD][/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD]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)]'[/TD]
[TD]74-82[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Excel 2007

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),""),""),"")

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<thead>[TR]

</thead><tbody>
</tbody>

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



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