I am trying to convert some data retaining the information from a string variable and replacing the name into new columns. These data contain all LEED variables for all regions in New Zealand from 2006-2018.

The structure of the data currently looks like this
Code:
. list measure_industry_region_year value in 1/15

     +---------------------------------------------------------------------------+
     |                                    measure_industry_region_year     value |
     |---------------------------------------------------------------------------|
  1. |      Job destructionAccommodation and food servicesAuckland2005      2835 |
  2. |      Job destructionAccommodation and food servicesAuckland2006    3067.5 |
  3. |      Job destructionAccommodation and food servicesAuckland2007   3301.25 |
  4. |      Job destructionAccommodation and food servicesAuckland2008    3287.5 |
  5. |      Job destructionAccommodation and food servicesAuckland2009    3152.5 |
     |---------------------------------------------------------------------------|
  6. |      Job destructionAccommodation and food servicesAuckland2010   3221.25 |
  7. |      Job destructionAccommodation and food servicesAuckland2011   3086.25 |
  8. |      Job destructionAccommodation and food servicesAuckland2012   3793.75 |
  9. |      Job destructionAccommodation and food servicesAuckland2013   3323.75 |
 10. |      Job destructionAccommodation and food servicesAuckland2014   3541.25 |
     |---------------------------------------------------------------------------|
 11. |      Job destructionAccommodation and food servicesAuckland2015   4018.75 |
 12. |      Job destructionAccommodation and food servicesAuckland2016   4166.25 |
 13. |      Job destructionAccommodation and food servicesAuckland2017      4835 |
 14. |      Job destructionAccommodation and food servicesAuckland2018   5246.25 |
 15. | Job destructionAccommodation and food servicesBay of Plenty2005       510 |
     +---------------------------------------------------------------------------+
What I need to do in order to merge with an existing data set is create a new format that would look like this:
Region Job Destruction_Accomodation year
Auckland 2835 2005
Auckland 3067.5 2006
I'm stumped as to how to decompose the measure_industry_region_year variable while also retaining the value for each row in the data. Any suggestions are much appreciated.

Best,

Davia