Need help with a function to Find and Replace

[bbenny01]

Hello, I would like to know if there is a function that can find multiple shorten year ranges in a cell and replace it with the full year range.

Example of shorten year range:
Buick LaCrosse 16-14, Regal 17-14
Mazda 18-14 3, 18-14 3 Sport
Hyundai 17-16 Tucson, Kia: 17 Sportage

Updated to Full year range
LaCrosse 2016-2014, Regal 2017-2014
Mazda 2018-2014 3, 2018-2014 3 Sport
Hyundai 2017-2016 Tucson, Kia: 2017 Sportage

I did try using the SUBSTITUTE function with a wildcard but it did not work.
Here is an example function I used for Buick LaCrosse 16-14, Regal 17-14 is in cell A1:
=SUBSTITUTE(A1,"1*-1*","201*-201*")
It returned the same exact data: LaCrosse 16-14, Regal 17-14

Your help will be greatly appreciated!

 

[Fluff]

Hi & welcome to MrExcel.
Unfortunately you cannot use wildcards in substitute.
This will do the date ranges in your example, but not the solo years.

+Fluff New.xlsm
	A	B
1
2	Buick LaCrosse 16-14, Regal 17-14	Buick LaCrosse 2016-2014, Regal 2017-2014
3	Mazda 18-14 3, 18-14 3 Sport	Mazda 2018-2014 3, 2018-2014 3 Sport
4	Hyundai 17-16 Tucson, Kia: 17 Sportage	Hyundai 2017-2016 Tucson, Kia: 17 Sportage
Lookup

Cell Formulas
Range	Formula
B2:B4	B2	=SUBSTITUTE(REPLACE(IFERROR(REPLACE(A2,FIND("-",A2,FIND("-",A2&"-")+1)-2,0,20),A2),FIND("-",A2)-2,0,20),"-","-20")

 

[bbenny01]

Fluff, Thank you so much!!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[bbenny01]

Hi Fluff, the function works for cells with 2 beginning years.

Example below

=SUBSTITUTE(REPLACE(IFERROR(REPLACE(G48,FIND("-",G48,FIND("-",G48&"-")+1)-2,0,20),G48),FIND("-",G48)-2,0,20),"-","-20")

GLK250 15-13, GLK350 15-10

will convert to

GLK250 2015-2013, GLK350 2015-2010

If the same function is applied to the line below - This has about 18 year ranges

535i GT 16-10, 535i GT xDrive 16-11, 550i 16-11, 550i GT 15-11, 550i GT xDrive 15-11, 550i xDrive 15-11, 650i 16-12, 650i Gran Coupe 16-13, 650i xDrive 16-12, 650i xDrive Gran Coupe 16-13, 740i 15-11, 740Ld xDrive 15, 740Li 15-11, 740Li xDrive 15-13, 750i 15-09, 750i xDrive 15-10, 750Li 15-09, 750Li xDrive 15-10, ActiveHybrid 7 15-13

It only converts up to the 2nd beginning year.

535i GT 2016-2010, 535i GT xDrive 2016-2011, 550i 16-2011, 550i GT 15-2011, 550i GT xDrive 15-2011, 550i xDrive 15-2011, 650i 16-2012, 650i Gran Coupe 16-2013, 650i xDrive 16-2012, 650i xDrive Gran Coupe 16-2013, 740i 15-2011, 740Ld xDrive 15, 740Li 15-2011, 740Li xDrive 15-2013, 750i 15-2009, 750i xDrive 15-2010, 750Li 15-2009, 750Li xDrive 15-2010, ActiveHybrid 7 15-2013

There are some year ranges I'm working with that go up to 35+

Hope this makes sense....

 

[bbenny01]

Forgot to mention the function does convert the ending years...

 

[Fluff]

Ok, in that case I think you really need a UDF.

Function bbenny(Txt As String) As String
   Dim Sp As Variant
   Dim i As Long
  
   Sp = Split(Txt)
   For i = 0 To UBound(Sp)
      If InStr(1, Sp(i), "-") > 0 Then
         Sp(i) = "20" & Left(Sp(i), 2) & "-20" & Mid(Sp(i), 4)
      End If
   Next i
   bbenny = Join(Sp, " ")
End Function

+Fluff New.xlsm
	A	B
1
2	Buick LaCrosse 16-14, Regal 17-14	Buick LaCrosse 2016-2014, Regal 2017-2014
3	Mazda 18-14 3, 18-14 3 Sport	Mazda 2018-2014 3, 2018-2014 3 Sport
4	Hyundai 17-16 Tucson, Kia: 17 Sportage	Hyundai 2017-2016 Tucson, Kia: 17 Sportage
5	535i GT 16-10, 535i GT xDrive 16-11, 550i 16-11, 550i GT 15-11, 550i GT xDrive 15-11, 550i xDrive 15-11, 650i 16-12, 650i Gran Coupe 16-13, 650i xDrive 16-12, 650i xDrive Gran Coupe 16-13, 740i 15-11, 740Ld xDrive 15, 740Li 15-11, 740Li xDrive 15-13, 750i 15-09, 750i xDrive 15-10, 750Li 15-09, 750Li xDrive 15-10, ActiveHybrid 7 15-13	535i GT 2016-2010, 535i GT xDrive 2016-2011, 550i 2016-2011, 550i GT 2015-2011, 550i GT xDrive 2015-2011, 550i xDrive 2015-2011, 650i 2016-2012, 650i Gran Coupe 2016-2013, 650i xDrive 2016-2012, 650i xDrive Gran Coupe 2016-2013, 740i 2015-2011, 740Ld xDrive 15, 740Li 2015-2011, 740Li xDrive 2015-2013, 750i 2015-2009, 750i xDrive 2015-2010, 750Li 2015-2009, 750Li xDrive 2015-2010, ActiveHybrid 7 2015-2013
Lookup

Cell Formulas
Range	Formula
B2:B5	B2	=bbenny(A2)

 

[bbenny01]

Thank U some much Fluff!!!!!

 

[Fluff]

You're welcome & thanks for the feedback.