Excel Macro

macro.vbs

'To get this to work you need to add the regexp reference.
'From Visual Basic: Tools -> References... make sure "Microsoft VBScript Regular Expressions 5.5" is checked
Sub breakUpMultiYearRows()
    'doesn't work on mac
    Dim r As New VBScript_RegExp_55.RegExp
    r.Pattern = "(\d{4})\s*-\s*(\d{4})"
      
    Dim Matches, Match
    
    Dim originalSelection As Range
    
    Dim firstYear, lastYear As Integer
    
    Dim matchFound As Boolean
    
    Do Until Selection.Value = ""
      Set originalSelection = Selection
      
      Set Matches = r.Execute(Selection.Value)
    
      matchFound = False
      
      For Each Match In Matches
        matchFound = True
        
        firstYear = Match.SubMatches(0)
        lastYear = Match.SubMatches(1)
        
        originalSelection.EntireRow.Insert
    
        originalSelection.EntireRow.Copy Destination:=originalSelection.Offset(-1, 0).EntireRow
        
        originalSelection.Offset(-1, 0).Value = firstYear
        
        If (firstYear + 1) = lastYear Then
          originalSelection.Value = lastYear
        Else
          originalSelection.Value = (firstYear + 1) & "-" & lastYear
        End If
        
      Next
    
      Selection.Offset(1, 0).Select
    Loop

End Sub