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

 

[Haui]

Thanks, guys.

In the meantime I stumbled over this: http://dailydoseofexcel.com/archives/2008/06/26/identify-empty-cells-in-vba/

I believe the following comments from 2008 are still the status quo:

<time datetime="2008-06-27T06:31:00-05:00">June 27, 2008 at 6:31 am </time>

@jgluck: “Is there any way create an empty (null) cell?”

No. We have suggested numerous times that MS add something like a NULL() or BLANK() worksheet formula that would act as a truly blank cell. This would help with downstream calculations, charting, and presentation. We’re still waiting.

<time datetime="2008-06-27T21:25:00-05:00">June 27, 2008 at 9:25 pm </time>
can you create a null cell (as a function result)?
no I don’t think so, by definition, by putting something in it (the formula) you are changing it from a null cell to some other type. That seems fair enough to me. =NA() is the next best thing for graphs etc.
If you enter =A2 in A1 where A2 is empty A1 returns 0. Excel coerces that empty cell to a number of value zero. If it didn’t, blanks would propagate all over and break select special stuff for example.
If you change A2 to be =”” then A1 looks blank, but its value is really a zero length string. If you paste the value over A2 you get a cell that looks and feels blank, nothing in the formula bar etc. But it is not a blank cell its a string cell with a zero length string. This means its not ISBLANK() and its not isEmpty() but it is the equivalent of vbNullString.

The topic is also addressed here fairly exhaustively:
https://www.excelanytime.com/excel/index.php?option=com_content&view=article&id=267:excel-vba-empty-zls-null-nothing-missing&catid=79&Itemid=475

Empty, Blank, ZLS (zero-length string), null string & vbNullString​

ZLS means a zero-length string (""), is also referred to as a null string, and has a length of zero (0). For all practical purposes using vbNullString is equivalent to a zero-length string ("") because VBA interprets both in a similar manner, though both are actually not the same - a 'zero length string' actually means creating a string with no characters, whereas vbNullString is a constant used for a null pointer meaning that no string is created and is also more efficient or faster to execute than ZLS. You can use "" or vbNullString alternatively in your code and both behave similarly. Note that there is no Blank keyword in vba, but we can refer to 'blank cells' or "empty cells" in Excel spreadsheet. There are Excel worksheet functions for empty cells: (i) the COUNTA function counts the number of cells that are not empty, and also counts or includes a cell with empty text ("") - also referrred to as empty string or zero length string - which is not counted as an empty cell; and (ii) the ISBLANK function returns True for an empty cell, and does not treat a zero-length string ("") as a blank (empty cell) similarly as in COUNTA. Both the worksheet functions of ISBLANK and COUNTA distinguish between an empty cell and a cell containing a zero-length string (ie. "" as formula result).
​

The route via VBA points to various forums which mention the fact that vbanullstring may be slightly preferable from a programming perspective over ="" as the latter uses more (or actually any) memory.

For me this mostly concludes the matter/my questions.

Cheers