excel - Split at delimiter and add to new line -

i have following data in excel:

and end resembling below:

there quite large amount of data manipulate seek efecient way of doing this, in essence involve:

• searching column region -
• if found, grab data start of - till next instance of - or end of cell
• copy data new row accompanying extended data (as per second screenshot)
• loop

please let me know if further information required , thank in advance

raw data below:

current: state   region type frequency   region  time    selected medians , 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 , 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 

with current data on active worksheet , state in a1, run macro.

sub split_and_create()     dim rw long, lr long, lc long, v long, vstats variant, vregns variant      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)         rw = 2 lr             vstats = application.index(.cells(rw, 1).resize(1, lc).value, 1, 0)             vregns = split(vstats(4), " - ")             v = lbound(vregns) ubound(vregns)                 vstats(4) = vregns(v)                 .cells(rows.count, 1).end(xlup).offset(1, 0).resize(1, lc) = vstats             next v         next rw     end end sub 

the proposed results should populate below current similar this.