Excel Paste Special Returns a ' instead of a blank?

Question

I have a formula which reads:
=IF(AT5=0,"",AT5).
For those cells which end up printing blank, if I then paste special values to get rid of the formula, instead of returning a blank cell, it results in a hidden tick (a ') - which only appears in the formula bar but is not visible in the cell. Does anyone know how to get around this? I would be enormously grateful.

Answer 1

i get that ' if i copy-paste-value the formula so you're not crazy. ;o) that's because excel puts an empty text string in there and not a blank cell. the ' signifies text.

why do you have that formula? can you simply choose not to display zero values? go to TOOLS-OPTIONS-VIEW and under "Window options" uncheck "Zero values". it'll hide all zero values in your entire workbook.

if you just want to hide zero values for a particular cell, then use cell formatting. highlight the cell(s) you want to hide zeros and hit [CTRL]+1 (or go to FORMAT-CELLS). in the Number tab, under the Category window, pick Custom. in the "Type:" box enter

general;general;""

you can obviously use formatting besides "general" but the key is the third format past the last semi-colon has to be "".

otherwise, in order to delete those zero/blank/empty formula results you could also change your formula to

=if(at5=0,na(),at5)

your cell will show "#N/A" in those cells which refer to blank cells. you can then do a search for formula errors. press F5, [CTRL]+G or edit-go to and select "special". then click the "formulas" radio button, uncheck everything except "Errors" and then the "OK" button. that will highlight all cells with errors and then you can simply hit the delete button on your keyboard.

Answer 2

I cannot replicate this either. I would suggest re-entering the formula. The only way to get a single apostrophe is if it is inside the quotation marks (although then it shows in the cell as an apostrophe, unless you have some special formatting which would cause it to display as a blank.)

Answer 3

I couldn't replicate that. The single quote did not appear. But it does present a problem. The cell is not a blank. I suggest you replace with something else eg '9a9a9' or some impossible sequence adn then do a search and replace function.

Answer 4

it is not somewhat what you like even yet it fairly is going to divide information into labelled communities by potential of letter. you additionally can substitute the colour to make it stand out out of your familiar information. In cellular A2 enter =REPT("A ",5), cellular A3 =REPT("B ",5) etc to A27=REPT("Z ",5) substitute the font coloration if required, upload all the different alphabetical information into col A then variety. The cells wth the different letters will variety to the strategies-blowing of each and every corresponding team of information with the comparable first letter. could make it much less complicated to seek for a particular team particularly than a sparkling cellular.