I have found a solution to my question above re copying text data from sheet 1 to sheet 3 within the same workbook. (the formatting in sheet 1 was causing the problem).
My next problem is that where there is a blank in sheet 1 I need it to show as a blank in sheet 3 (at the moment it is showing a 0 because of the formula).
Could anyone please help with this. I would be very grateful