Jul 06 2022 12:06 PM
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):
Regular Reference (with zeroes I would like to replace with blanks):
Attempt #1 (len()=0 formula):
Attempt #2 (isblank()=true):
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!
Jul 06 2022 12:24 PM
Jul 06 2022 12:31 PM - edited Jul 06 2022 06:38 PM
SolutionYou 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
Jul 06 2022 12:44 PM
You may use
=SUBSTITUTE( Sheet1!A1:A68, "", "" )
IF() doesn't work with elements of the array with more than 255 characters long
Jul 06 2022 12:52 PM
Jul 12 2022 09:56 AM
Jul 12 2022 11:17 AM
Jul 06 2022 12:31 PM - edited Jul 06 2022 06:38 PM
SolutionYou 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