Text Manipulation

If we assume you strings are in A1:A6 then

B1:
=IF(MAX($A1:A1)=RIGHT(SUBSTITUTE($A1,"PRESENT",CURRENY_YEAR),4)+0,"",LEFT($A1,4)+COLUMNS($B1:B1)-1)
copied across matrix  B1:F6 (or however far across you deem necessary)

There's a fair amount of repetition going on in the above though fairly lightweight...

Sorry, the screen editor played havoc with the layout of the data. Sorry for the confusion, it should look like: (Ignore the ...'s as they are there only to force the correct look of the two columns of data)

______COL A__________________COL B_________
ROW 1|2005-2009..................|.2005, 2006, 2007, 2008, 2009
ROW 2|2010-PRESENT..........|.2010, 2011

Thank you again for considering my problem!

Al

If you want a delimited single cell string then I would opt for a UDF

Function SplitYears(rngY As String, Optional strDelim As String = "-") As String
    Dim vYrs As Variant
    On Error GoTo Handler:
    vYrs = Split(rngY, strDelim)
    If Not IsNumeric(vYrs(1)) Then vYrs(1) = Range("CURRENT_YEAR")
    vYrs = Evaluate("TRANSPOSE(ROW(" & vYrs(0) & ":" & vYrs(1) & "))")
    SplitYears = Join(vYrs, ",")
    Exit Function
Handler:
    SplitYears = "Invalid"
End Function

The above, stored in a module in VBE in a Macro Enabled file can be used along the lines of:

B1: =SPLITYEARS(A1)
copied down

Sorry I should have thought of that.

Also, in retrospect it would be much (much) better to pass CURRENT_YEAR named range as an argument in the UDF call so as to make it a direct precedent
(altering CURRENT_YEAR would in turn recalculate all cells using the UDF without need for Volatility)

Function SplitYears(rngY As String, rngCY As Range, Optional strDelim As String = "-") As String
    Dim vYrs As Variant
    On Error GoTo Handler:
    vYrs = Split(rngY, strDelim)
    If UBound(vYrs) = 0 Then
        SplitYears = vYrs(0)
    Else
        If Not IsNumeric(vYrs(1)) Then vYrs(1) = rngCY(1)
        vYrs = Evaluate("TRANSPOSE(ROW(" & vYrs(0) & ":" & vYrs(1) & "))")
        SplitYears = Join(vYrs, ",")
    End If
    Exit Function
Handler:
    SplitYears = "Invalid"
End Function

Now called with additional parameter, eg:

=SPLITYEARS(A1,CURRENT_YEAR)