Find and Replace Empty Texts ("")

[Jubinell]

Hi,

I'm having this problem whereby a few cells in my work contain empty texts. An empty text is represented textually in VBA with a pair of double quotation marks with nothing inside (I think).
This means that the cells don't display anything visible, but are not blank/empty.

So what happens is that formulas such as ISBLANK(), COUNTA(), etc. are returning unexpected results.

How do I find and replace these empty texts with nothingness?

If possible, no VBA please!

Thank you in advance

 

[GTO]

A worksheet function cannot change another cell. If you are wanting to delete spaces inside cells that appear empty but may have several spaces or other non-printing characters, you will need vba.

If vba is an option, please show some sample data using Richard Schollar's HTML Maker or Excel Jeanie.

 

[Jubinell]

Whoa not even find or replace can do the job?

By the way I'm sure what I have is neither a space nor a non-printing character. I don't think it's a character at all because I am not able to move the cursor anywhere other than the starting position once I'm inside these cells.

As for sample data, I don't think that's necessary. My task is relatively straightforward. Clear all content (spaces, non-printing characters, empty texts such as ""...all) from selected cells.

 

[GTO]

Sorry, I misunderstood.

If you cannot move inside the cell (arrow keys) while in edit mode, I think you must be correct as to no spaces. Have you tried entering =CODE(cell address) in another cell to see if a character is detected?

 

[Sandeep Warrier]

If you could install ASAP-Utilities, it has a feature called Advanced Character Removal. You can select Invisible Characters etc and replace them with nothing.

Edit: I'm guessing what you have are formula blanks. I faced this problem when some users used paste special > values on formulae that returned ""
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[Jubinell]

@GTO code() returns #Value! error.
@sandeep I think I might have done something like that. Any chance this could be done without costing money?

 

[Blade Hunter]

@GTO code() returns #Value! error.
@sandeep I think I might have done something like that. Any chance this could be done without costing money?

It could be done with VBA but you specified you don't want a VBA solution.

 

[Jubinell]

Well I said "if possible, no VBA please!"
If you have some codes in mind, please do let me know.

 

[Sandeep Warrier]

@GTO code() returns #Value! error.
@sandeep I think I might have done something like that. Any chance this could be done without costing money?

ASAP Utilities is a free utility
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[Jubinell]

@sandeep

ASAP works like a charm. It took off the invisible characters when I chose "Invisible" under Advanced Character Removal.

Thanks.

P.S. Didn't know that there's a free version of ASAP as well. This is cool.