if then formula excel to show blank cell

The ISBLANK function will only return true if the cell is empty, if the cell is blank the ISBLANK function will return false (a more accurate name for the ISBLANK function would be ISEMPTY!).

In our formula, the IF function checks to see if the length of cell A2 is 0 AND that cell A2 is not empty (this is represented by NOT(ISBLANK(A2)) in our formula), when these two criteria are met the formula returns ‘zero-length string’, when the criteria are not met the formula returns ‘Not a zero-length string’. =IF(AND(LEN(A2)=0,NOT(ISBLANK(A2))),'Zero-length string','Not a zero-length string')Ī breakdown of the formula which checks to see if a cell contains a zero-length string You can use the following formula to identify cells which contain zero-length strings: If a cell contains a constant zero-length string the formula bar will be empty even though the cell is not empty.Įxcel formula to determine if a cell contains a zero-length string Constant zero-length strings can also be imported into Excel from a database that contains zero-length strings. Zero-length strings formed from typing a single apostrophe into a cell: when an apostrophe ' appears as the first character in a cell, it is used to set the cell format to text and inserts a zero-length string into the cell.Ĭonstant zero-length strings: these can result from a copy and paste as values operation of one of the two types of zero-length strings mentioned above. Zero-length strings output by a formula: a zero-length string can be entered into Excel by typing two consecutive double quotation marks e.g. There are 3 types of zero-length strings in Excel: A zero-length string, also known as an empty string is a string that is zero characters long, as a result the LEN function returns 0 when a cell contains a zero-length string. In Microsoft Excel both blank and empty cells display as empty, however empty cells do not contain a formula or value, whilst blank cells contain a formula or value that evaluates to or represents a zero-length string. If the cell contains a regular space or a non-breaking space the LEN function will return a number greater than 0. Regular spaces can be removed by using the TRIM function and non-breaking spaces can be removed by using a formula. If a cell appears to be empty but the ISBLANK function returns false, it is for one of 3 reasons:

If then formula excel to show blank cell