Split at delimiter and add to new line

Question

I have the following data in Excel:

and would like end up with something resembling the below:

There is quite a large amount of data to manipulate so I seek the most efecient way of doing this, in essence it would involve:

• Searching column Region for -
• If found, grab data from start of - till next instance of - or end of cell
• Copy data into new row with accompanying extended data (as per the second screenshot)
• Loop

Please let me know if further information is required and thank you in advance

Raw data below:

Current:
State   Region Type Frequency   Region  Time    Selected Medians and Averages   Value
New South Wales Statistical Area Level 2    Annual  Eden    2011    Median age of persons   47
New South Wales Statistical Area Level 2    Annual  Eurobodalla Hinterland  2011    Median age of persons   48
New South Wales Statistical Area Level 2    Annual  Merimbula - Tura Beach - Moss Beach 2011    Median age of persons   51
New South Wales Statistical Area Level 2    Annual  Moruya - Tuross Head    2011    Median age of persons   50

Proposed:
State   Region Type Frequency   Region  Time    Selected Medians and Averages   Value
New South Wales Statistical Area Level 2    Annual  Eden    2011    Median age of persons   47
New South Wales Statistical Area Level 2    Annual  Eurobodalla Hinterland  2011    Median age of persons   48
New South Wales Statistical Area Level 2    Annual  Merimbula   2011    Median age of persons   51
New South Wales Statistical Area Level 2    Annual  Tura Beach  2011    Median age of persons   51
New South Wales Statistical Area Level 2    Annual  Moss Beach  2011    Median age of persons   51
New South Wales Statistical Area Level 2    Annual  Moruya  2011    Median age of persons   50
New South Wales Statistical Area Level 2    Annual  Tuross Head 2011    Median age of persons   50

Answer 1

With only your Current data on the active worksheet and State in A1, run this macro.

Sub split_and_create()
    Dim rw As Long, lr As Long, lc As Long, v As Long, vSTATs As Variant, vREGNs As Variant

    With ActiveSheet
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        lc = .Cells(1, Columns.Count).End(xlToLeft).Column
        .Cells(1, 2).CurrentRegion.Rows(1).Copy _
            Destination:=.Cells(lr + 2, 1)
        For rw = 2 To lr
            vSTATs = Application.Index(.Cells(rw, 1).Resize(1, lc).Value, 1, 0)
            vREGNs = Split(vSTATs(4), " - ")
            For v = LBound(vREGNs) To UBound(vREGNs)
                vSTATs(4) = vREGNs(v)
                .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, lc) = vSTATs
            Next v
        Next rw
    End With
End Sub

The Proposed results should populate below the Current similar to this.