more questions than answers, trying to remove the ' from in front of text in imported text

Hi, new here, I have more questions than answers. But I'm trying to remove a lot of ' "apostrophes" from the leading cells in imported text. I have copied the formula to another spreadsheet, and it appears to remove the text, however, excel still doesn't treat as a formula. If I click on the cell, and press enter, then for example a date will shift to the right as it interpretes it as a value. Is there a way to not have to click on every cell on the spreadsheet. Many columns and many rows. Also on the same subject, someone suggested the =substitute(a1,char(39),""). This also removed the text but again the text is not treated as a date or value etc. Finally, there was a suggestion to use the (value). Some times the cell doesn't contain a value but text, so the get(value) if I am remembering correctly doesn't work there.
Seems like these things gets rid of the ' but you cannot use excell to reference those cells and get any meaningful results.

I know I've got three or four things going on here, but I keep thinking that there should be an easy answer, but now I'm just not sure.

Any help would be much appreciated.