A cell's Range.PrefixCharacter property will tell what that 'prefix' is, so
If Range("A1").PrefixCharacter = "'" Then ...
should allow you to detect the apostrophe.

To insert text with this prefix, just enter the value as you would type it,
i.e. (note the apostophe inside the first quotation mark):
Range("B1").Value = "'456"

"William Benson" wrote:

Is there a test (in VBA) specifically whether a cell has an apostrophe in
the formula ...i.e., that it has been hardcoded by the user as text?
Similarly, is there a way through VBA to insert the same hard-coded text
into a cell.

Thanks.