This seems somewhat implausible, due to the oddness that there're no concerns about ADDING the apostrophe to mirror the concerns about trying to remove the apostrophe.
But... whatever. The stated problem aches for VBA. It excels at monkey-work. Both ends of the problem, adding the apostrophe and removing it. But many can't or won't use VBA so consider the following approach (one of several that come to mind).
What is it? Don't use an apostrophe! Pick some incredibly unlikely to exist otherwise character and use it. Then Find and Replace will work as expected to remove it. So no problem ever arises. Perhaps try "˛", the good old "Ogonek" or something equally odd from whatever font you have formatted the data with.
But if not having the problem to start with is too radical an idea, try this:
First, format the data using a Style. Set it up as you please. Then copy the data, or portions of it until completely copied, to a new sheet using Paste|Special|Values. If you do that back onto the original locations, things remain as they are. If you do it to a clean location, none of the effects mentioned will go with the data.
Second, apply your Style to the copied data so it is formatted as desired. Done. None of the unfortunate things exist in the new place.
Third, do the dressing up things that always exist after the headline problem is solved. An example would be that the data simply MUST be in the original place. No problem. Just copy everything back overtop the orginal data using regular copy and paste so that ALL of the original formatting malarky gets overwritten. (You are correct in that Excel sees the initial "-" as starting a formula, and that is not the only initial character that will trigger that ("=" of course!, "+", and "@" will as well). However, the apostrophe is formatting (left-aligned) (the double quote " leading off is right-aligned, the "^" is center-aligned).) Everyone has a unique set of dressing up things to consider, but none of them keep the above from working.
