SOLVED

Reference to Sheet - Returning Blank if Cell is Blank #VALUE! Error

New Contributor

I'm trying to reference text cells from Sheet1 and insert blanks for blank references (as opposed to zeros). When using isblank() or len()=0, I receive #VALUE! errors for the longer text cells. 

 

Sheet1 (sheet to be referenced):

1.PNG

 

Regular Reference (with zeroes I would like to replace with blanks):

2.PNG

 

 

Attempt #1 (len()=0 formula):

3.PNG

 

 

 

 

Attempt #2 (isblank()=true):

4.PNG

 

 

Does anyone have any ideas on formulas I can use to reference blanks as blanks (not zeroes) without getting errors on my longer text references? Thank you!

6 Replies
yes this is a limitation and there are some workarounds I believe members here came up with BUT in your case I would recommend just using a custom number format on the column to display 0 value as blank (""). Open number format goto 'more number formats', select "Custom" and then under the "Type:" entry bar type: 0;-0;
best response confirmed by daisyblazey (New Contributor)
Solution

@daisyblazey 

You could use:

 

 

=MAP(Sheet1!A1:A68,LAMBDA(x,IF(x=0,"",x)))

 

 

 

The FILTER function can remove 0s but it would also contract the array.  MAP will keep the numbers and blank out the 0s. 

 

Another solution would be to custom format out the 0s.  For example, custom formatting code: 

0;-0;;General

 

 

@daisyblazey 

You may use

=SUBSTITUTE( Sheet1!A1:A68, "", "" )

IF() doesn't work with elements of the array with more than 255 characters long

Neat use of MAP! Not that I had realised that the original formula would fail. ISTEXT also works inside the Lambda function.
The MAP formula worked as it preserved the number type for cells with different types! Thank you so much!