This can be easily achieved with the help of Text to Columns inbuilt feature as suggested by AB33.

Follow these steps to achieve the desired output...

On Sheet Sheet1-Before

1) Delete rows 1, 2 and 4.

2) Select whole column A.

3) On Data Tab --> Text to Columns --> Make sure Delimited radio button is selected --> Next --> Select Semicolon under Delimiters options --> Next --> Finish

That's it.