When I paste>special>value, I get a ' or ^ at the target cell. Why

[TWT]

I created a formular at source cell, such that it shows an empty cell if
there are no values in it: Eg. =IF(D14<>"-",COUNTIF(Data!R:R,D14),"")

When I copy the source cell, and do a Paste>Special>Value onto a target
cell, I get a ' in the target cell, instead of it being blank cell.

When I use this on my colleague's excel file, after I do a
Paste>Special>Value, the target cell on his pc shows a blank cell and not the
'.

Why is there a difference and what formatting caused it? I need the target
cell to be blank instead of having a '.

Many thanks in advance.

 

[Dave Peterson]

Those character as known as prefixcharacters.

You can see an apostrophe (') for a left-justified label, " for a
right-justified label, ^ for a centered label, \ for a repeated label, or blank.

I think they were kept so that Excel would be compatible with Lotus 123 files.


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

In code you could do something like:

Option Explicit
Sub testme()
With ActiveSheet
With .cells 'or a specific range: With .Range("D")
.Replace what:="", replacement:="$$$$$", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace what:="$$$$$", replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
End With
End With
End Sub