Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

If you need to do this lots, you can record a macro when you do it manually.

=================
If you know that you're going to convert to values when you're done, you could
modify your formulas to make it slightly easier:

=IF(MID(A5,LEN(A5),1)="q",A5&"ue",na())
or even
=IF(right(A5,1)="q",A5&"ue",na())

Then just change #n/a to nothing after you convert to values.


Joe wrote:

Cell A5 contains the text word TEST

Cell C5 contains the formula =IF(MID(A5,LEN(A5),1)="q",A5&"ue","")

The result is a "blank" or a "" type of cell content since the result is
FALSE.

Now if I select this cell and copy it and then PASTE/Special into the same
C5 position I get a single quote ' to appear on the formula bar but not in
the cell when C5 is selected.
Placing the formula = code(C5) into cell D5 gives #VALUE!
Does anyone have an explanation as to what might be going on here?

--

Dave Peterson