Formula to replace zero-length string

[Haui]

Hi, few sort of follow-up questions to this thread here: https://www.mrexcel.com/forum/excel...er-explanation-null-empty-nothing-solved.html

1) Is there a formula-solution (i.e. no VBA and no text to columns as I understand that solutions do exist with these tools) to convert a zero-length, non-blank string to a true empty cell, e.g. so that the isblank-function yiels TRUE for a cell which contains a fomula whose cell_reference is e.g. the following cell: =substitute([target_cell],[target_cell_contents],) (which is equivalent to: =substitute([target_cell],[target_cell_contents],"")?

2) How can such zero-length, non-blank string be used in Excel's FIND/FIND-REPLACE Functionm, not sure if possible at all as there's no UNICODE char or similar available for ""?

Thanks

 

[steve the fish]

No formula can convert another cell.

 

[steve the fish]

As an aside i dont think i ever use ISBLANK. I use ="" or <>"".

 

[Haui]

No formula can convert another cell.

But I can say for example: =if([target_cell]="",[IF=TRUE]).

And if the target cell contains a zero-length string, i.e. evaluates to true. So then my question is: what needs to go into the [IF_TRUE] argument to create a cell-result where the ISBLANK-function evaluates to TRUE?

btw - re your other points that you prefer ="" instead of ISBLANK is exactly part of my question as both formulas produce DIFFERENT results for a truly empty cell vs. a zero-length non-blank cell.
A similar point is highlighted in my above link pointing out the inconsistent behaviour of ISBLANK vs. e.g. COUNTBLANK. The same reasoning applies to your formula.

Thanks

 

[Fluff]

If you have a formula in a cell then ISBLANK will return false, there is no way round that, that I know of.

 

[steve the fish]

Its just the way the application works. For me ISBLANK is inconsistent insofar that if i was unaware of the presence of the formula, or even a paste special values of that formula, it would produce an erroneous result. For that reason i dont use it.

 

[mikerickson]

If you have a formula in a cell then ISBLANK will return false, there is no way round that, that I know of.

If there is a formula in a cell, then the cell isn't blank. (It has a formula in it)

 

[steve the fish]

If i use ="" then paste special values over that cell ISBLANK sees something that i dont and produces FALSE. Yet a blank cell that ISBLANK produces TRUE is equal to my cell. Logically for me that is nonsense. For that reason i avoid its use.

 

[Haui]

If there is a formula in a cell, then the cell isn't blank. (It has a formula in it)

That point I sort of understand, however: If I paste the cell contents of ="" into a new cell, both cells yield FALSE if I use ISBLANK().
However, if I go into the cell where ="" was pasted into with F2 and then press enter, ISBLANK() evalutes to TRUE.

Why is that then?

 

[mikerickson]

That point I sort of understand, however: If I paste the cell contents of ="" into a new cell, both cells yield FALSE if I use ISBLANK().
However, if I go into the cell where ="" was pasted into with F2 and then press enter, ISBLANK() evalutes to TRUE.

Why is that then?

Don't look at the .Value property of the cell, look at the .Formula property of the cell

The worksheet formula =ISBLANK(A1) returns the same as [VBA] Range("A1").Formula = vbNullString