Expanding Years 2008-2012 to 2008 2009 2010 2011 2012

[Garu]

Hello Everyone,

FYI I did a search on the forums ,but couldn't find any relevant information. I'm looking for a formula that can take this " Dodge Challenger 2008-2012" to " Dodge Challenger 2008 2009 2010 2011 2012". I'm basically looking for a formula that can expand the years with the text fixed. Now I have a formula that does the opposite which crunches the years. I have provided a work book with that information maybe someone can figure it out as I tried but couldn't. Please any input would be gladly appreciated.

Thank You!Year Crunching.xls

[Jakobshavn]

Try the following User Defined Function (UDF):

Public Function YearExpander(stri As String) As String
Dim N1 As Long, N2 As Long, U As Long
ary = Split(stri, " ")
U = UBound(ary)
s2 = ary(U)
N1 = CLng(Split(s2, "-")(0))
N2 = CLng(Split(s2, "-")(1))
ary(U) = ""
YearExpander = Join(ary, " ")
For L = N1 To N2
    YearExpander = YearExpander & " " & L
Next
End Function

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I

ALT-M opens a fresh module

3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=YearExpander(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx

for specifics on UDFs

Macros must be enabled for this to work!

[Pete_UK]

With "Dodge Challenger 2008-2012" in A6 (without the quotes), this formula in B6 will cope with a range of up to 6 years (inclusive):

=LEFT(A6,FIND("-",A6)-1)&IF(MID(A6,FIND("-",A6)-4,4)+1>--RIGHT(A6,4),""," "&MID(A6,FIND("-",A6)-4,4)+1)&IF(MID(A6,FIND("-",A6)-4,4)+2>--RIGHT(A6,4),""," "&MID(A6,FIND("-",A6)-4,4)+2)&IF(MID(A6,FIND("-",A6)-4,4)+3>--RIGHT(A6,4),""," "&MID(A6,FIND("-",A6)-4,4)+3)&IF(MID(A6,FIND("-",A6)-4,4)+4>--RIGHT(A6,4),""," "&MID(A6,FIND("-",A6)-4,4)+4)&IF(MID(A6,FIND("-",A6)-4,4)+5>--RIGHT(A6,4),""," "&MID(A6,FIND("-",A6)-4,4)+5)

That looks a bit daunting, so I have manually split it below so that you can see how it works:

=LEFT(A6,FIND("-",A6)-1)
&IF(MID(A6,FIND("-",A6)-4,4)+1>--RIGHT(A6,4),""," "&MID(A6,FIND("-",A6)-4,4)+1)
&IF(MID(A6,FIND("-",A6)-4,4)+2>--RIGHT(A6,4),""," "&MID(A6,FIND("-",A6)-4,4)+2)
&IF(MID(A6,FIND("-",A6)-4,4)+3>--RIGHT(A6,4),""," "&MID(A6,FIND("-",A6)-4,4)+3)
&IF(MID(A6,FIND("-",A6)-4,4)+4>--RIGHT(A6,4),""," "&MID(A6,FIND("-",A6)-4,4)+4)
&IF(MID(A6,FIND("-",A6)-4,4)+5>--RIGHT(A6,4),""," "&MID(A6,FIND("-",A6)-4,4)+5)

If you need more years, just copy the last bit of the formula and change the +5 (twice) to +6, +7 etc.

There should be no spaces either side of the hyphen, and it assumes a 4-digit year from and to.

Hope this helps.

Pete

[Garu]

Thank you everyone both formulas totally worked. I appreciate your input.