I have spent hours trying to work this out myself to no avail.
I have a cell formatted as text, to allow the use of leading zero's (with no fixed format, there might two leading zero's and 8 other didgits, or one zero and two other digits or no leading zero, so using 00### or the like doesn't help). That's fine 98% of the time, but on occasion the user needs to search for data using a vlookup formula. To make this happen, a macro is used to add a formula to the cell (and remove the formula after the task is complete).
As soon as the formula is placed in the text formatted cell, everyone knows the formula will be displayed instead of the result.
What can I do programmatically to stop the formula being displayed instead of the result, while maintaining the text format of the cell?